import os
import sys
import tempfile
import uuid
import functools
sys.path.append(os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),'modules'))
sys.path.append(os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__)))))
import ganzhi
import week_yun
import ktt_order_export
import ktt_core
import ktt_core_new
import pandas as pd
import read_config
from flask import Flask, request,jsonify, Response,render_template,send_file,make_response, session, redirect, url_for
import zipfile
import io
from datetime import datetime,timedelta
import json
import xlwings as xw
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import pymysql
import platform
if platform.system().lower()=='windows':
    import pythoncom
    import win32com.client
elif platform.system().lower()=='darwin':
    pass

import dd_ppt
import base64
import subprocess
import random

from PIL import Image
import re

def login_required(f):
    """登录验证装饰器"""
    @functools.wraps(f)
    def decorated_function(*args, **kwargs):
        if 'logged_in' not in session or not session['logged_in']:
            return redirect(url_for('ej_login'))
        return f(*args, **kwargs)
    return decorated_function


class EjService(Flask):

    def __init__(self,*args,**kwargs):
        super(EjService, self).__init__(*args, **kwargs)
        config_fn=os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),'configs','ej_service.config')
        self.config_ej=read_config.read_json(config_fn)
        
        # 设置session密钥
        self.secret_key = 'ej_jade_secret_key_2024'
        
        # 定义登录凭据（不使用数据库）
        self.login_credentials = {
            'admin': 'admin',
            'ejj': 'ejj1234'
        }

        # 添加登录相关路由
        self.add_url_rule('/ej_login', view_func=self.ej_login, methods=['GET', 'POST'])
        self.add_url_rule('/ej_logout', view_func=self.ej_logout, methods=['GET'])

        #读取快团团有关配置文件存放路径
        ktt_config=os.path.join(os.path.dirname(__file__),'config','ktt.config')
        with open(ktt_config,'r',encoding='utf-8') as f:
            self.ktt_config=json.loads(f.read())

        # print(self.ktt_config)
        #读取不同发货商导表时与快团团对应的规格名称
        with open(self.ktt_config['ktt_col_map_config'],'r',encoding='utf-8') as f:
            self.col_map_config=json.loads(f.read())

        # 读取不同发货商发货表的列名
        with open(self.ktt_config['col_config_fn'],'r',encoding='utf-8') as f:
            self.config_ktt_order=json.loads(f.read())

        # 读取规格的默认名称返回前端
        with open(self.ktt_config['page_config_fn'],'r',encoding='utf-8') as f:
            config_page_default=json.loads(f.read())
        self.spec0=config_page_default['spec0']
        self.spec1=config_page_default['spec1']
        self.fn_info=config_page_default['fn_info']
        self.sender_name=config_page_default['sender_name']
        self.sender_tel=config_page_default['sender_tel']

        
        #路由
        #渲染页面
        #首页
        self.add_url_rule('/riyun',view_func=self.riyun)

        # 快团团
        self.add_url_rule('/ktt', view_func=self.ktt,methods=['GET','POST'])
        #快团团导单
        self.add_url_rule('/ktt_buy_list', view_func=self.ktt_buy_list_page,methods=['GET','POST'])
        #处理快团团导单
        self.add_url_rule('/ktt_deal_list', view_func=self.ktt_deal_list,methods=['GET','POST'])
        # 快团团结果打包并返回前端
        self.add_url_rule('/zip_and_download_ktt_exp_order', view_func=self.zip_and_download_ktt_exp_order,methods=['GET','POST'])
        #获取不同供应商下的不同规格的在表格中的名称（与快团团名称有对应，但不一定相同）
        self.add_url_rule('/ktt_return_spec', view_func=self.ktt_return_spec,methods=['GET','POST'])
        #渲染茶叶入库页面
        self.add_url_rule('/ktt_purchase_tea', view_func=self.ktt_purchase_tea_page,methods=['GET','POST'])
        #渲染茶叶出库页面
        self.add_url_rule('/ktt_sale_tea', view_func=self.ktt_sale_tea_page,methods=['GET','POST'])
        #渲染茶叶查询页面
        self.add_url_rule('/ktt_query_tea', view_func=self.ktt_query_tea_page,methods=['GET','POST'])
        #渲染茶叶菜单页面
        self.add_url_rule('/ktt_tea_menu', view_func=self.ktt_tea_menu_page,methods=['GET','POST'])
        #渲染快团团发送订单文本页面
        self.add_url_rule('/ktt_send_txt', view_func=self.ktt_send_txt_page,methods=['GET','POST'])
        #快团团导入文件生成订单页面
        self.add_url_rule('/ktt_import_order', view_func=self.ktt_import_order_page,methods=['GET','POST'])
        # 快团团综合订单结果打包并返回前端
        self.add_url_rule('/zip_and_download_mix_ktt', view_func=self.zip_and_download_mix_ktt,methods=['GET','POST'])
        #渲染随笔页面
        self.add_url_rule('/suibi', view_func=self.suibi_page,methods=['GET','POST'])
        # 快团团综合订单结果打包并返回前端
        self.add_url_rule('/zip_and_download_suibi', view_func=self.zip_and_download_suibi,methods=['GET','POST'])
        # 处理随笔生成请求
        self.add_url_rule('/generate_suibi', view_func=self.generate_suibi,methods=['GET','POST'])
        #处理随笔图片查询
        self.add_url_rule('/check_suibi_pic', view_func=self.check_suibi_pic,methods=['GET','POST'])


        #处理前端
        #获取操作人信息
        self.add_url_rule('/ktt_fetch_opr', view_func=self.ktt_fetch_opr,methods=['GET','POST'])
        #获取产品列表
        self.add_url_rule('/ktt_fetch_product', view_func=self.ktt_fetch_product,methods=['GET','POST'])
        #获取规格列表
        self.add_url_rule('/ktt_fetch_specs', view_func=self.ktt_fetch_specs,methods=['GET','POST'])
        #处理入库
        self.add_url_rule('/ktt_purchase_deal',view_func=self.ktt_purchase_deal,methods=['GET','POST'])
        #处理出库
        self.add_url_rule('/ktt_sale_deal',view_func=self.ktt_sale_deal,methods=['GET','POST'])
        #返回统计剩余数量        
        self.add_url_rule('/ktt_stat_deal',view_func=self.ktt_stat_deal,methods=['GET','POST'])
        #获取包装列表
        self.add_url_rule('/ktt_fetch_pkgs', view_func=self.ktt_fetch_pkgs,methods=['GET','POST'])     
        #获取ktt_html页面设置
        self.add_url_rule('/get_ktt_html_page_config', view_func=self.get_ktt_html_page_config,methods=['GET','POST'])     
        #上传文件
        self.add_url_rule('/upload_file', view_func=self.upload_file,methods=['GET','POST'])     
        self.add_url_rule('/upload_pic_suibi', view_func=self.upload_pic_suibi,methods=['GET','POST'])   
        #查看日运或随笔视频日否已生成
        self.add_url_rule('/check_riyun_files',view_func=self.check_riyun_files,methods=['GET','POST'])
        self.add_url_rule('/check_suibi_files',view_func=self.check_suibi_files,methods=['GET','POST'])
        
        
        #处理ktt的xlsx文件，生成综合订单
        self.add_url_rule('/deal_ktt_export', view_func=self.deal_ktt_export,methods=['GET','POST'])     
        
          

        #日运录入
        self.add_url_rule('/riyun_input_page',view_func=self.riyun_input_page,methods=['GET','POST'])
        
        
        # 生成日运
        self.add_url_rule('/generate_riyun', view_func=self.generate_riyun,methods=['GET','POST'])

        # 结果打包并返回前端
        self.add_url_rule('/zip_and_download', view_func=self.zip_and_download,methods=['GET','POST'])


        # 写入日运表
        self.add_url_rule('/write_into_riyun_xlsx', view_func=self.write_into_riyun_xlsx,methods=['GET','POST'])
        # 写入日运表
        self.add_url_rule('/riyun_menu', view_func=self.riyun_menu,methods=['GET','POST'])

        #紫微斗数页面
        self.add_url_rule('/zwds', view_func=self.zwds_page,methods=['GET','POST'])
        
        #快团团新页面
        self.add_url_rule('/new_ktt_page',view_func=self.new_ktt_page,methods=['GET','POST'])
        #快团团新页面设置
        self.add_url_rule('/get_new_ktt_page_config',view_func=self.get_new_ktt_page_config,methods=['GET','POST'])      
        #快团团新页面处理
        self.add_url_rule('/deal_submit_ktt_orders',view_func=self.deal_submit_ktt_orders,methods=['GET','POST'])       
        #下载订单文件
        self.add_url_rule('/download_orders', view_func=self.download_orders, methods=['GET'])
        self.add_url_rule('/new_ktt_deal_xlsx_from_page', view_func=self.new_ktt_deal_xlsx_from_page, methods=['POST'])
        #快团团订单下载指南
        self.add_url_rule('/how_to_exp_order', view_func=self.how_to_exp_order, methods=['GET'])


        #处理学习星球小红书图片 
        self.add_url_rule('/generate_xhs', view_func=self.generate_xhs_page,methods=['GET','POST'])
        #处理学习星球小红书图片生成
        self.add_url_rule('/generate_xhs_image', view_func=self.generate_xhs_image,methods=['GET','POST'])
        #处理学习星球小红书封面图片页面
        self.add_url_rule('/generate_xhs_cover', view_func=self.generate_xhs_cover_page,methods=['GET','POST'])
        #处理学习星球小红书封面图片生成
        self.add_url_rule('/generate_xhs_cover_image', view_func=self.generate_xhs_cover_image,methods=['GET','POST'])
        #处理学习星球markdown转图片
        self.add_url_rule('/md_to_pic', view_func=self.md_to_pic,methods=['GET','POST'])
        #处理双栏markdown转图片
        self.add_url_rule('/md_to_pic2', view_func=self.md_to_pic2,methods=['GET','POST'])
        #Markdown图片转视频
        self.add_url_rule('/md_pic_to_video',view_func=self.md_pic_to_video,methods=['POST'])   
        #苔花社双栏页面
        self.add_url_rule('/md_to_pic2',view_func=self.md_to_pic2_page,methods=['GET','POST'])
        #获取学习星球的背景图列表
        self.add_url_rule('/get_ddstudy_bg_list',view_func=self.get_ddstudy_bg_list,methods=['GET','POST'])
        #提供玉石产品图片服务
        self.add_url_rule('/serve_jade_image',view_func=self.serve_jade_image,methods=['GET'])
        
        #提供背景图片服务
        self.add_url_rule('/serve_bg_image',view_func=self.serve_bg_image,methods=['GET'])


        #提供ejj珠宝页面查询
        self.add_url_rule('/ej_index',view_func=self.ej_index_page,methods=['GET','POST'])
        self.add_url_rule('/ej_jade_search',view_func=self.ej_jade_search_page,methods=['GET','POST'])
        self.add_url_rule('/ej_culture_search',view_func=self.ej_culture_search_page,methods=['GET','POST'])
        self.add_url_rule('/ej_stock_input',view_func=self.ej_stock_input_page,methods=['GET','POST'])
        self.add_url_rule('/ej_sale_stat',view_func=self.ej_sale_stat_page,methods=['GET','POST'])
        
        #提供ejj珠宝input页面查询
        self.add_url_rule('/ej_product_input',view_func=self.ej_product_input_page,methods=['GET','POST'])
        #提供文创产品录入页面
        self.add_url_rule('/ej_culture_product_input',view_func=self.ej_culture_product_input_page,methods=['GET','POST'])
        #提供ejj珠宝销售结算页面
        self.add_url_rule('/ej_sale_settle',view_func=self.ej_sale_settle_page,methods=['GET','POST'])
        #测试移动版界面
        self.add_url_rule('/test_mobile',view_func=self.test_mobile_page,methods=['GET'])
        #提供ejj珠宝销售录入页面
        self.add_url_rule('/ej_sale_input',view_func=self.ej_sale_input_page,methods=['GET','POST'])
        #提供ejj珠宝销售库存导出页面
        self.add_url_rule('/ej_export_sale_list',view_func=self.ej_export_sale_list_page,methods=['GET','POST'])
        #导出销售列表Excel
        self.add_url_rule('/export_sale_excel',view_func=self.export_sale_excel,methods=['POST'])
        #返回ej数据库product_table数据
        self.add_url_rule('/get_jade_product_list',view_func=self.get_jade_product_list,methods=['GET','POST'])
        #获取文创产品列表
        self.add_url_rule('/get_culture_product_list',view_func=self.get_culture_product_list,methods=['GET','POST'])
        #获取有效库存编号
        self.add_url_rule('/get_valid_stock_ids',view_func=self.get_valid_stock_ids,methods=['GET','POST'])
        #获取导出库存数据
        self.add_url_rule('/get_export_stock_data',view_func=self.get_export_stock_data,methods=['GET','POST'])
        #get_ejj_jade_config
        self.add_url_rule('/get_ejj_jade_json_config',view_func=self.get_ejj_jade_json_config,methods=['GET','POST'])
        #处理库存和结算信息
        self.add_url_rule('/deal_stock_and_settle',view_func=self.deal_stock_and_settle,methods=['POST'])
        #处理销售信息
        self.add_url_rule('/deal_ej_sale',view_func=self.deal_ej_sale,methods=['POST'])        
        #获取结算数据
        self.add_url_rule('/get_settlement_data',view_func=self.get_settlement_data,methods=['GET'])
        #更新结算信息
        self.add_url_rule('/update_settlement_stock',view_func=self.update_settlement_stock,methods=['POST'])
        #批量结算库存
        self.add_url_rule('/batch_settle_stocks',view_func=self.batch_settle_stocks,methods=['POST'])
        #库存结算页面
        self.add_url_rule('/ej_stock_settle',view_func=self.ej_stock_settle_page,methods=['GET'])
        #获取玉石产品图片列表
        self.add_url_rule('/get_jade_images',view_func=self.get_jade_images,methods=['GET'])
        #获取玉石配置信息
        self.add_url_rule('/get_jade_config',view_func=self.get_jade_config,methods=['GET'])
        #获取玉石销售结算数据
        self.add_url_rule('/get_sale_settle_data',view_func=self.get_sale_settle_data,methods=['GET','POST'])
        #更新销售结算数据
        self.add_url_rule('/update_sale_settlement',view_func=self.update_sale_settlement,methods=['GET','POST'])
        #获取销售统计数据
        self.add_url_rule('/get_sale_stat_data',view_func=self.get_sale_stat_data,methods=['GET','POST'])
        #导出日清单
        self.add_url_rule('/export_daily_xlsx',view_func=self.export_daily_xlsx,methods=['GET','POST'])
        #录入新产品
        self.add_url_rule('/save_jade_product',view_func=self.save_jade_product,methods=['GET','POST'])
        #保存文创产品信息
        self.add_url_rule('/save_culture_product',view_func=self.save_culture_product,methods=['GET','POST'])
        #处理封面图片
        self.add_url_rule('/deal_jade_cover_img',view_func=self.deal_jade_cover_img,methods=['GET','POST'])
        #按购买人搜索
        self.add_url_rule('/search_by_buyer',view_func=self.search_by_buyer,methods=['GET','POST'])
        #产品ID模糊查询
        self.add_url_rule('/search_product_ids',view_func=self.search_product_ids,methods=['GET','POST'])
        #获取产品详情
        self.add_url_rule('/get_product_details',view_func=self.get_product_details,methods=['GET','POST'])
        #上传图片页面
        self.add_url_rule('/upload_product_image',view_func=self.upload_product_image,methods=['GET','POST'])
        #处理上传图片页面
        self.add_url_rule('/ej_upload_product_image_page',view_func=self.ej_upload_product_image_page,methods=['GET','POST'])
        #处理退单页面
        self.add_url_rule('/return_stock',view_func=self.return_stock_page,methods=['GET','POST'])
        # 获取可退单的销售记录
        self.add_url_rule('/get_sales_for_return', view_func=self.get_sales_for_return, methods=['POST'])
        # 创建退单记录
        self.add_url_rule('/create_return', view_func=self.create_return, methods=['POST'])
        # ej登录页面
        self.add_url_rule('/ej_login', view_func=self.ej_login_page, methods=['POST'])
        # 查询库存
        self.add_url_rule('/get_product_stock_info', view_func=self.get_product_stock_info, methods=['GET','POST'])



        #jk用的记账页面
        self.add_url_rule('/jk_account',view_func=self.jk_account_page,methods=['GET','POST'])

        #jk用的写入数据库
        self.add_url_rule('/jk_account_input',view_func=self.jk_account_input,methods=['GET','POST'])

        # 记账系统API路由
        self.add_url_rule('/jk/get_categories', view_func=self.jk_get_categories, methods=['GET'])
        self.add_url_rule('/jk/save_settlement', view_func=self.jk_save_settlement, methods=['POST'])
        self.add_url_rule('/jk/get_settlements', view_func=self.jk_get_settlements, methods=['GET'])
        self.add_url_rule('/jk/get_summary', view_func=self.jk_get_summary, methods=['GET'])
        self.add_url_rule('/jk/get_accounts', view_func=self.jk_get_accounts, methods=['GET'])
        self.add_url_rule('/jk/get_account_summary', view_func=self.jk_get_account_summary, methods=['GET'])
        self.add_url_rule('/jk/get_category_summary', view_func=self.get_category_summary, methods=['GET'])
        self.add_url_rule('/jk/get_account_config', view_func=self.jk_get_account_config, methods=['GET'])
        self.add_url_rule('/jk/get_projects', view_func=self.jk_get_projects, methods=['GET'])
        self.add_url_rule('/jk/delete_settlement', view_func=self.jk_delete_settlement, methods=['POST'])
        self.add_url_rule('/jk/update_settlement', view_func=self.jk_update_settlement, methods=['POST'])
        self.add_url_rule('/batch_update_sale_settlement', view_func=self.batch_update_sale_settlement, methods=['POST'])
        
    




    ######jk记账#####
    def connect_mysql_jk(self):
        with open(os.path.join(os.path.dirname(os.path.realpath((__file__))),'config','db_jk.config'),'r',encoding='utf-8') as f:
            cfg=json.load(f)
        # 连接数据库
        conn = pymysql.connect(
            host=cfg['host'],       # 数据库主机地址
            user=cfg['user'],     # 数据库用户名
            password=cfg['password'], # 数据库密码
            database=cfg['database'],  # 要连接的数据库名称
            port=cfg['port']
        )

        return conn

    def return_stock_page(self):
        return render_template('/return_stock.html')        

    def get_sales_for_return(self):
        """获取可退单的销售记录"""
        try:
            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 查询销售记录，包含产品信息和库存信息，排除已退单的记录
            sql = """
                SELECT 
                    s.id, s.sale_datetime, s.buyer_name, s.stock_id, 
                    s.sale_qty, s.sale_real_price, s.sale_total_price,
                    s.settlement_status, s.settled_price, s.comment,
                    st.product_id, p.product_name, p.type_1, p.type_2,
                    st.material_price, st.manual_price, st.transfer_price, st.other_price,
                    s.is_returned, s.return_id
                FROM sales_table s
                LEFT JOIN stock_table st ON s.stock_id = st.stock_id
                LEFT JOIN product_table p ON st.product_id = p.product_id
                WHERE s.settlement_status IN ('completed', 'partial', 'pending')
                AND s.is_returned = 0
                ORDER BY s.sale_datetime DESC
            """
            
            cursor.execute(sql)
            res = cursor.fetchall()
            
            fields = [
                "id", "sale_datetime", "buyer_name", "stock_id",
                "sale_qty", "sale_real_price", "sale_total_price",
                "settlement_status", "settled_price", "comment",
                "product_id", "product_name", "type_1", "type_2",
                "material_price", "manual_price", "transfer_price", "other_price",
                "is_returned", "return_id"
            ]
            
            sales_data = self.res_to_dic(res, fields)
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'data': sales_data,
                'message': f'成功获取 {len(sales_data)} 条销售记录'
            })
            
        except Exception as e:
            print(f"获取销售记录失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取销售记录失败: {str(e)}'
            }), 500

    def create_return(self):
        """创建退单记录"""
        try:
            data = request.json
            print("接收到的退单数据:", data)
            
            # 验证必填字段
            required_fields = ['stock_id', 'return_date', 'return_reason', 'return_qty', 'return_amount']
            for field in required_fields:
                if not data.get(field):
                    return jsonify({
                        'success': False,
                        'message': f'缺少必填字段: {field}'
                    }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 开始事务
            conn.begin()
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 生成退单编号
            return_id = f"RT{data['return_date'].replace('-', '')}{random.randint(1000, 9999)}"
            
            # 1. 构建插入退单记录的SQL语句
            return_sql = """
                INSERT INTO return_stock_table (
                    return_id, stock_id, product_id, return_date, return_reason,
                    return_qty, return_amount, comment,
                    created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            # 准备插入值
            return_values = (
                return_id,
                data['stock_id'],
                data.get('product_id'),
                data['return_date'],
                data['return_reason'],
                float(data['return_qty']),
                float(data['return_amount']),
                data.get('comment', ''),
                current_time,
                current_time
            )
            
            # 执行退单记录插入
            cursor.execute(return_sql, return_values)
            
            # 2. 更新对应销售记录的退单状态
            # 首先找到对应的销售记录（最新的未退单记录）
            find_sale_sql = """
                SELECT id FROM sales_table 
                WHERE stock_id = %s AND is_returned = 0 
                ORDER BY sale_datetime DESC 
                LIMIT 1
            """
            cursor.execute(find_sale_sql, (data['stock_id'],))
            sale_record = cursor.fetchone()
            
            if sale_record:
                sale_id = sale_record[0]
                # 更新销售记录的退单状态
                update_sale_sql = """
                    UPDATE sales_table 
                    SET is_returned = 1, return_id = %s, updated_at = %s
                    WHERE id = %s
                """
                cursor.execute(update_sale_sql, (return_id, current_time, sale_id))
            
            # 提交事务
            conn.commit()
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': '退单记录创建成功并更新了销售记录状态',
                'return_id': return_id
            })
            
        except Exception as e:
            print(f"创建退单记录失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'创建退单记录失败: {str(e)}'
            }), 500


    def jk_account_page(self):
        return render_template('/jk_account.html')
    
    def jk_account_input(self):
        pass

    # 获取分类数据
    def jk_get_categories(self):
        try:
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 获取分类数据
            category_sql = "SELECT category_id, category_name, category_type, sort_order, is_active FROM j_settlement_category WHERE is_active = TRUE ORDER BY sort_order"
            cursor.execute(category_sql)
            categories = cursor.fetchall()
            
            # 获取子分类数据
            subcategory_sql = "SELECT subcategory_id, category_id, subcategory_name, sort_order, is_active FROM j_settlement_subcategory WHERE is_active = TRUE ORDER BY sort_order"
            cursor.execute(subcategory_sql)
            subcategories = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            # 转换为字典格式
            category_fields = ["category_id", "category_name", "category_type", "sort_order", "is_active"]
            subcategory_fields = ["subcategory_id", "category_id", "subcategory_name", "sort_order", "is_active"]
            
            categories_dic = self.res_to_dic(categories, category_fields)
            subcategories_dic = self.res_to_dic(subcategories, subcategory_fields)
            
            return jsonify({
                'success': True,
                'categories': categories_dic,
                'subcategories': subcategories_dic
            })
            
        except Exception as e:
            print(f"获取分类数据失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取分类数据失败: {str(e)}'
            }), 500

    # 保存结算记录
    def jk_save_settlement(self):
        try:
            data = request.json
            print("接收到的结算数据:", data)
            
            # 验证必填字段
            required_fields = ['settlement_date', 'settlement_type', 'category_id', 'subcategory_id', 
                             'item_name', 'quantity', 'unit_price', 'settler_name']
            for field in required_fields:
                if not data.get(field):
                    return jsonify({
                        'success': False,
                        'message': f'缺少必填字段: {field}'
                    }), 400

            # 连接数据库
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 生成结算单号（如果未提供）
            settlement_id = data.get('settlement_id')
            if not settlement_id:
                # 生成格式：年月日+4位随机数
                date_part = data['settlement_date'].replace('-', '')
                random_part = str(random.randint(1000, 9999))
                settlement_id = f"JS{date_part}{random_part}"
            
            # 计算金额
            quantity = float(data['quantity'])
            unit_price = float(data['unit_price'])
            amount = quantity * unit_price
            
            # 构建插入SQL（排除amount字段，因为它是生成列）
            sql = """
                INSERT INTO j_settlement_main (
                    settlement_id, settlement_date, settlement_type, category_id, subcategory_id,
                    item_name, project_name, settled_with, quantity, unit_price, settler_name,
                    accounted_status, settled_amount, account_name, payment_method, comment,
                    created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            # 处理settled_amount字段
            settled_amount = float(data.get('settled_amount', 0))
            
            values = (
                settlement_id,
                data['settlement_date'],
                data['settlement_type'],
                data['category_id'],
                data['subcategory_id'],
                data['item_name'],
                data.get('project_name', 'ejj'),  # 添加project_name字段，默认值为ejj
                data.get('settled_with', ''),  # 添加settled_with字段
                quantity,
                unit_price,
                data['settler_name'],
                data.get('accounted_status', 'pending'),  # 使用新的accounted_status字段
                settled_amount,  # 添加settled_amount字段
                data.get('account_name'),
                data.get('payment_method'),
                data.get('comment', ''),
                current_time,
                current_time
            )
            
            cursor.execute(sql, values)
            conn.commit()
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': '结算记录保存成功',
                'settlement_id': settlement_id
            })
            
        except Exception as e:
            print(f"保存结算记录失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'保存失败: {str(e)}'
            }), 500

    # 获取结算记录
    def jk_get_settlements(self):
        try:
            # 获取查询参数
            start_date = request.args.get('start_date')
            end_date = request.args.get('end_date')
            type_filter = request.args.get('type')
            settled_filter = request.args.get('settled')
            accounted_filter = request.args.get('accounted')
            project_filter = request.args.get('project_name')
            
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 构建基础SQL
            sql = """
                SELECT 
                    s.settlement_id, s.settlement_date, s.settlement_type, s.category_id, s.subcategory_id,
                    s.item_name, s.project_name, s.settled_with, s.quantity, s.unit_price, s.amount, s.settler_name,
                    s.accounted_status, s.settled_amount, s.account_name, s.payment_method, s.comment,
                    s.created_at, s.updated_at,
                    c.category_name, sc.subcategory_name
                FROM j_settlement_main s
                LEFT JOIN j_settlement_category c ON s.category_id = c.category_id
                LEFT JOIN j_settlement_subcategory sc ON s.subcategory_id = sc.subcategory_id
                WHERE 1=1
            """
            params = []
            
            # 添加筛选条件
            if start_date and end_date:
                sql += " AND s.settlement_date BETWEEN %s AND %s"
                params.extend([start_date, end_date])
            
            if type_filter:
                sql += " AND s.settlement_type = %s"
                params.append(type_filter)
            
            if settled_filter:
                sql += " AND s.is_settled = %s"
                params.append(settled_filter == 'true')
            
            if accounted_filter:
                sql += " AND s.accounted_status = %s"
                params.append(accounted_filter)
            
            if project_filter:
                sql += " AND s.project_name = %s"
                params.append(project_filter)
            
            sql += " ORDER BY s.settlement_date DESC, s.created_at DESC"

            # print('jk_get_settlements() sql:',sql)
            
            cursor.execute(sql, params)
            records = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            # 转换为字典格式
            fields = [
                "settlement_id", "settlement_date", "settlement_type", "category_id", "subcategory_id",
                "item_name", "project_name", "settled_with", "quantity", "unit_price", "amount", "settler_name",
                "accounted_status", "settled_amount", "account_name", "payment_method", "comment",
                "created_at", "updated_at", "category_name", "subcategory_name"
            ]
            
            records_dic = self.res_to_dic(records, fields)
            
            return jsonify({
                'success': True,
                'records': records_dic
            })
            
        except Exception as e:
            print(f"获取结算记录失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取记录失败: {str(e)}'
            }), 500
    
    # 获取分类统计汇总
    def get_category_summary(self):
        try:
            # 获取查询参数
            start_date = request.args.get('start_date')
            end_date = request.args.get('end_date')
            type_filter = request.args.get('type')
            project_name = request.args.get('project_name')
            
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 构建基础SQL，按一级分类和二级分类统计
            sql = """
                SELECT 
                    s.settlement_type,
                    c.category_id,
                    c.category_name,
                    sc.subcategory_id,
                    sc.subcategory_name,
                    SUM(s.amount) as total_amount
                FROM j_settlement_main s
                LEFT JOIN j_settlement_category c ON s.category_id = c.category_id
                LEFT JOIN j_settlement_subcategory sc ON s.subcategory_id = sc.subcategory_id
                WHERE s.settlement_date BETWEEN %s AND %s
            """
            params = [start_date, end_date]
            
            if type_filter:
                sql += " AND s.settlement_type = %s"
                params.append(type_filter)
            
            if project_name:
                sql += " AND s.project_name = %s"
                params.append(project_name)
            
            sql += " GROUP BY s.settlement_type, c.category_id, c.category_name, sc.subcategory_id, sc.subcategory_name"
            sql += " ORDER BY c.sort_order, sc.sort_order"
            
            cursor.execute(sql, params)
            summary_data = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            # 计算汇总信息
            total_income = 0
            total_expense = 0
            category_details = []
            
            for record in summary_data:
                settlement_type, category_id, category_name, subcategory_id, subcategory_name, amount = record
                amount = float(amount or 0)
                
                if settlement_type == 'income':
                    total_income += amount
                else:
                    total_expense += amount
                
                # 查找是否已存在该分类的记录
                category_found = False
                for detail in category_details:
                    if detail['category_id'] == category_id and detail['subcategory_id'] == subcategory_id:
                        if settlement_type == 'income':
                            detail['income'] += amount
                        else:
                            detail['expense'] += amount
                        category_found = True
                        break
                
                # 如果没找到，添加新记录
                if not category_found:
                    category_details.append({
                        'category_id': category_id,
                        'category_name': category_name,
                        'subcategory_id': subcategory_id,
                        'subcategory_name': subcategory_name,
                        'income': amount if settlement_type == 'income' else 0,
                        'expense': amount if settlement_type == 'expense' else 0
                    })
            
            net_income = total_income - total_expense
            
            return jsonify({
                'success': True,
                'summary': {
                    'total_income': total_income,
                    'total_expense': total_expense,
                    'net_income': net_income,
                    'category_details': category_details
                }
            })
            
        except Exception as e:
            print(f"获取分类统计汇总失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取分类统计失败: {str(e)}'
            }), 500

    # 获取统计汇总
    def jk_get_summary(self):
        try:
            # 获取查询参数
            start_date = request.args.get('start_date')
            end_date = request.args.get('end_date')
            category_id = request.args.get('category_id')
            
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 构建基础SQL
            sql = """
                SELECT 
                    s.settlement_type,
                    c.category_id,
                    c.category_name,
                    SUM(s.amount) as total_amount
                FROM j_settlement_main s
                LEFT JOIN j_settlement_category c ON s.category_id = c.category_id
                WHERE s.settlement_date BETWEEN %s AND %s
            """
            params = [start_date, end_date]
            
            if category_id:
                sql += " AND s.category_id = %s"
                params.append(category_id)
            
            sql += " GROUP BY s.settlement_type, c.category_id, c.category_name"
            
            cursor.execute(sql, params)
            summary_data = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            # 计算汇总信息
            total_income = 0
            total_expense = 0
            category_details = []
            
            for record in summary_data:
                settlement_type, category_id, category_name, amount = record
                amount = float(amount or 0)
                
                if settlement_type == 'income':
                    total_income += amount
                else:
                    total_expense += amount
                
                category_details.append({
                    'category_id': category_id,
                    'category_name': category_name,
                    'income': amount if settlement_type == 'income' else 0,
                    'expense': amount if settlement_type == 'expense' else 0
                })
            
            net_income = total_income - total_expense
            
            return jsonify({
                'success': True,
                'summary': {
                    'total_income': total_income,
                    'total_expense': total_expense,
                    'net_income': net_income,
                    'category_details': category_details
                }
            })
            
        except Exception as e:
            print(f"获取统计汇总失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取统计失败: {str(e)}'
            }), 500

    # 获取账户列表
    def jk_get_accounts(self):
        try:
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 获取所有不重复的账户名称
            sql = "SELECT DISTINCT account_name FROM j_settlement_main WHERE account_name IS NOT NULL AND account_name != '' ORDER BY account_name"
            cursor.execute(sql)
            accounts = [row[0] for row in cursor.fetchall()]
            
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'accounts': accounts
            })
            
        except Exception as e:
            print(f"获取账户列表失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取账户列表失败: {str(e)}'
            }), 500

    # 获取账户配置信息
    def jk_get_account_config(self):
        try:
            # 读取账户配置文件
            config_path = os.path.join(os.path.dirname(__file__), 'config', 'jk_account_config.json')
            
            if not os.path.exists(config_path):
                return jsonify({
                    'success': False,
                    'message': '账户配置文件不存在'
                }), 404
            
            with open(config_path, 'r', encoding='utf-8') as f:
                account_config = json.load(f)
            
            return jsonify({
                'success': True,
                'data': account_config
            })
            
        except Exception as e:
            print(f"获取账户配置失败: {str(e)}")
            return jsonify({
                'success': False,
                'message': f'获取账户配置失败: {str(e)}'
            }), 500

    # 获取项目列表
    def jk_get_projects(self):
        try:
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 获取所有不重复的项目名称
            sql = "SELECT DISTINCT project_name FROM j_settlement_main WHERE project_name IS NOT NULL AND project_name != '' ORDER BY project_name"
            cursor.execute(sql)
            projects = [row[0] for row in cursor.fetchall()]
            
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'projects': projects
            })
            
        except Exception as e:
            print(f"获取项目列表失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取项目列表失败: {str(e)}'
            }), 500

    # 按账户获取统计汇总
    def jk_get_account_summary(self):
        try:
            # 获取查询参数
            start_date = request.args.get('start_date')
            end_date = request.args.get('end_date')
            account_name = request.args.get('account_name')
            
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 构建基础SQL
            sql = """
                SELECT 
                    s.settlement_type,
                    s.account_name,
                    SUM(s.amount) as total_amount
                FROM j_settlement_main s
                WHERE s.settlement_date BETWEEN %s AND %s
            """
            params = [start_date, end_date]
            
            if account_name:
                sql += " AND s.account_name = %s"
                params.append(account_name)
            else:
                sql += " AND s.account_name IS NOT NULL AND s.account_name != ''"
            
            sql += " GROUP BY s.settlement_type, s.account_name ORDER BY s.account_name, s.settlement_type"

            print('sql:',sql)
            
            cursor.execute(sql, params)
            summary_data = cursor.fetchall()
            
            # 计算账户余额
            account_balances = {}
            for record in summary_data:
                settlement_type, account_name, amount = record
                amount = float(amount or 0)
                
                if account_name not in account_balances:
                    account_balances[account_name] = {
                        'income': 0,
                        'expense': 0,
                        'balance': 0
                    }
                
                if settlement_type == 'income':
                    account_balances[account_name]['income'] += amount
                    account_balances[account_name]['balance'] += amount
                else:
                    account_balances[account_name]['expense'] += amount
                    account_balances[account_name]['balance'] -= amount
            
            # 转换为列表格式
            account_details = []
            for account_name, balance in account_balances.items():
                account_details.append({
                    'account_name': account_name,
                    'income': balance['income'],
                    'expense': balance['expense'],
                    'balance': balance['balance']
                })
            
            # 计算总计
            total_income = sum(item['income'] for item in account_details)
            total_expense = sum(item['expense'] for item in account_details)
            total_balance = sum(item['balance'] for item in account_details)
            
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'summary': {
                    'total_income': total_income,
                    'total_expense': total_expense,
                    'total_balance': total_balance,
                    'account_details': account_details
                }
            })
            
        except Exception as e:
            print(f"获取账户统计汇总失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取账户统计失败: {str(e)}'
            }), 500

    # 删除结算记录
    def jk_delete_settlement(self):
        try:
            data = request.json
            settlement_id = data.get('settlement_id')
            
            if not settlement_id:
                return jsonify({
                    'success': False,
                    'message': '缺少结算单号参数'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 检查记录是否存在
            check_sql = "SELECT COUNT(*) FROM j_settlement_main WHERE settlement_id = %s"
            cursor.execute(check_sql, (settlement_id,))
            record_exists = cursor.fetchone()[0]
            
            if record_exists == 0:
                cursor.close()
                conn.close()
                return jsonify({
                    'success': False,
                    'message': f'结算记录 {settlement_id} 不存在'
                }), 404
            
            # 删除记录
            delete_sql = "DELETE FROM j_settlement_main WHERE settlement_id = %s"
            cursor.execute(delete_sql, (settlement_id,))
            conn.commit()
            
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': f'结算记录 {settlement_id} 删除成功'
            })
            
        except Exception as e:
            print(f"删除结算记录失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'删除失败: {str(e)}'
            }), 500

    # 更新结算记录
    def jk_update_settlement(self):
        try:
            data = request.json
            settlement_id = data.get('settlement_id')
            settled_amount = float(data.get('settled_amount', 0))
            settlement_date = data.get('settlement_date')
            
            if not settlement_id:
                return jsonify({
                    'success': False,
                    'message': '缺少结算单号参数'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_jk()
            cursor = conn.cursor()
            
            # 检查记录是否存在并获取当前信息
            check_sql = "SELECT amount, settled_amount FROM j_settlement_main WHERE settlement_id = %s"
            cursor.execute(check_sql, (settlement_id,))
            record = cursor.fetchone()
            
            if not record:
                cursor.close()
                conn.close()
                return jsonify({
                    'success': False,
                    'message': f'结算记录 {settlement_id} 不存在'
                }), 404
            
            current_amount = float(record[0] or 0)
            current_settled_amount = float(record[1] or 0)
            
            # 验证结算金额不能超过总金额
            if settled_amount > current_amount:
                cursor.close()
                conn.close()
                return jsonify({
                    'success': False,
                    'message': f'结算金额不能超过总金额 {current_amount}'
                }), 400
            
            # 计算新的结算状态
            if settled_amount >= current_amount:
                accounted_status = 'completed'  # 已入账
            elif settled_amount > 0:
                accounted_status = 'partial'    # 部分入账
            else:
                accounted_status = 'pending'    # 待入账
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 更新记录 - 如果提供了结算日期则更新，否则保持原日期
            if settlement_date:
                update_sql = """
                    UPDATE j_settlement_main 
                    SET settled_amount = %s, accounted_status = %s, settlement_date = %s, updated_at = %s
                    WHERE settlement_id = %s
                """
                cursor.execute(update_sql, (settled_amount, accounted_status, settlement_date, current_time, settlement_id))
            else:
                update_sql = """
                    UPDATE j_settlement_main 
                    SET settled_amount = %s, accounted_status = %s, updated_at = %s
                    WHERE settlement_id = %s
                """
                cursor.execute(update_sql, (settled_amount, accounted_status, current_time, settlement_id))
            
            conn.commit()
            
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': f'结算记录 {settlement_id} 更新成功',
                'data': {
                    'settled_amount': settled_amount,
                    'accounted_status': accounted_status,
                    'settlement_date': settlement_date
                }
            })
            
        except Exception as e:
            print(f"更新结算记录失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'更新失败: {str(e)}'
            }), 500

    def batch_settle_stocks(self):
        """批量结算库存项目"""
        try:
            data = request.json
            print("接收到的批量结算数据:", data)
            
            stock_ids = data.get('stock_ids', [])
            payer = data.get('payer', '晓')
            
            if not stock_ids:
                return jsonify({
                    'success': False,
                    'message': '缺少库存编号参数'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            settled_count = 0
            
            # 对每个库存编号处理所有需要结算的类型
            for stock_id in stock_ids:
                # 获取该库存的所有结算信息
                stock_sql = """
                    SELECT s.stock_id, s.settlement_type, s.amount, s.settled_amount, s.balance_amount,
                           st.material_price, st.manual_price, st.transfer_price, st.other_price, st.qty
                    FROM settlement_stock_table s
                    LEFT JOIN stock_table st ON s.stock_id = st.stock_id
                    WHERE s.stock_id = %s AND s.settlement_status IN ('pending', 'partial')
                """
                cursor.execute(stock_sql, (stock_id,))
                settlement_items = cursor.fetchall()
                
                if not settlement_items:
                    continue
                
                # 处理该库存的每个结算类型
                for item in settlement_items:
                    stock_id, settlement_type, amount, settled_amount, balance_amount, material_price, manual_price, transfer_price, other_price, qty = item
                    
                    # 计算需要支付的金额（剩余未支付金额）
                    payment_amount = float(balance_amount or 0)
                    
                    if payment_amount <= 0:
                        continue
                    
                    # 更新结算信息
                    update_sql = """
                        UPDATE settlement_stock_table 
                        SET settlement_status = 'completed',
                            settled_amount = settled_amount + %s,
                            balance_amount = 0,
                            settlement_payer = %s,
                            settlement_date = %s,
                            comment = CONCAT(IFNULL(comment, ''), %s),
                            updated_at = %s
                        WHERE stock_id = %s AND settlement_type = %s
                    """
                    
                    comment_addition = f"\n{current_time}: 批量结算支付{payer} ¥{payment_amount:.2f}"
                    update_values = (
                        payment_amount,
                        payer,
                        current_time,
                        comment_addition,
                        current_time,
                        stock_id,
                        settlement_type
                    )
                    
                    cursor.execute(update_sql, update_values)
                    settled_count += 1
            
            conn.commit()
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': f'批量结算成功',
                'settled_count': settled_count
            })
            
        except Exception as e:
            print(f"批量结算失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'批量结算失败: {str(e)}'
            }), 500
   
        
    #####珠宝######
    @login_required
    def ej_index_page(self):
        return render_template('/ej_sale_index.html') 

    def ej_jade_search_page(self):
        return render_template('/ej_jade_search.html') 
    
    def ej_culture_search_page(self):
        return render_template('/ej_culture_search.html') 
    
    @login_required
    def ej_sale_stat_page(self):
        return render_template('/ej_sale_stat.html') 
    
    @login_required
    def ej_sale_settle_page(self):
        return render_template('/ej_sale_settle.html') 
    
    
    @login_required
    def ej_stock_input_page(self):
        return render_template('/ej_stock_input.html') 
    
    @login_required
    def ej_stock_settle_page(self):
        return render_template('/ej_stock_settle.html') 
    
    @login_required
    def ej_product_input_page(self):
        return render_template('/ej_product_input.html') 
    
    @login_required
    def ej_culture_product_input_page(self):
        return render_template('/ej_culture_product_input.html') 
    
    @login_required
    def ej_sale_input_page(self):
        return render_template('/ej_sale.html')
    
    @login_required
    def ej_upload_product_image_page(self):
        return render_template('/ej_upload_product_image.html') 
    
    @login_required
    def ej_export_sale_list_page(self):
        return render_template('/ej_export_sale_list.html')

    

    def ej_login(self):
        """处理登录请求"""
        if request.method == 'POST':
            username = request.form.get('username')
            password = request.form.get('password')
            
            if username in self.login_credentials and self.login_credentials[username] == password:
                session['logged_in'] = True
                session['username'] = username
                # 重定向到之前访问的页面或首页
                next_page = request.args.get('next') or url_for('ej_index_page')
                return redirect(next_page)
            else:
                return render_template('/ej_login.html', error='用户名或密码错误')
        
        return render_template('/ej_login.html')

    def ej_logout(self):
        """处理登出请求"""
        session.pop('logged_in', None)
        session.pop('username', None)
        return redirect(url_for('ej_login'))

    def ej_login_page(self):
        return render_template('/ej_login.html')
    
    def get_product_stock_info(self):
        """获取产品库存信息，支持单个产品和批量查询"""
        try:
            # 支持GET和POST两种请求方式
            if request.method == 'POST':
                data = request.json
                product_ids = data.get('product_ids') if data else None
                product_id = data.get('product_id') if data else None
            else:
                product_ids = request.args.getlist('product_ids[]')
                product_id = request.args.get('product_id')
            
            # 确定要查询的产品ID列表
            target_product_ids = []
            if product_ids:
                if isinstance(product_ids, list):
                    target_product_ids = product_ids
                else:
                    target_product_ids = [product_ids]
            elif product_id:
                target_product_ids = [product_id]
            
            if not target_product_ids:
                return jsonify({
                    'success': False,
                    'message': '缺少产品ID参数'
                }), 400
            
            print('get product stock info for:', target_product_ids)
            
            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()

            # 构建批量查询的SQL
            if len(target_product_ids) == 1:
                # 单个产品查询
                sql = """
                    SELECT 
                        s.stock_id,
                        s.product_id,
                        s.qty as stock_qty,
                        COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0) as total_sale_qty,
                        (s.qty - COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0)) as available_qty,
                        s.material_price,
                        s.manual_price,
                        s.transfer_price,
                        s.other_price,
                        p.product_name,
                        p.type_1,
                        p.type_2,
                        p.wu_xings,
                        p.colors,
                        p.major_material,
                        p.second_material,
                        p.third_material,
                        p.accessories_material
                    FROM stock_table s
                    LEFT JOIN sales_table sa ON s.stock_id = sa.stock_id
                    LEFT JOIN product_table p ON s.product_id = p.product_id
                    WHERE s.product_id = %s
                    GROUP BY s.stock_id, s.product_id, s.qty, s.material_price, s.manual_price, s.transfer_price, s.other_price,
                             p.product_name, p.type_1, p.type_2, p.wu_xings, p.colors, p.major_material, p.second_material, 
                             p.third_material, p.accessories_material
                    ORDER BY s.stock_id
                """
                cursor.execute(sql, (target_product_ids[0],))
            else:
                # 批量产品查询
                placeholders = ','.join(['%s'] * len(target_product_ids))
                sql = f"""
                    SELECT 
                        s.stock_id,
                        s.product_id,
                        s.qty as stock_qty,
                        COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0) as total_sale_qty,
                        (s.qty - COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0)) as available_qty,
                        s.material_price,
                        s.manual_price,
                        s.transfer_price,
                        s.other_price,
                        p.product_name,
                        p.type_1,
                        p.type_2,
                        p.wu_xings,
                        p.colors,
                        p.major_material,
                        p.second_material,
                        p.third_material,
                        p.accessories_material
                    FROM stock_table s
                    LEFT JOIN sales_table sa ON s.stock_id = sa.stock_id
                    LEFT JOIN product_table p ON s.product_id = p.product_id
                    WHERE s.product_id IN ({placeholders})
                    GROUP BY s.stock_id, s.product_id, s.qty, s.material_price, s.manual_price, s.transfer_price, s.other_price,
                             p.product_name, p.type_1, p.type_2, p.wu_xings, p.colors, p.major_material, p.second_material, 
                             p.third_material, p.accessories_material
                    ORDER BY s.product_id, s.stock_id
                """
                cursor.execute(sql, target_product_ids)
            
            res = cursor.fetchall()
            
            fields = [
                "stock_id",
                "product_id", 
                "stock_qty",
                "total_sale_qty",
                "available_qty",
                "material_price",
                "manual_price",
                "transfer_price",
                "other_price",
                "product_name",
                "type_1",
                "type_2",
                "wu_xings",
                "colors",
                "major_material",
                "second_material",
                "third_material",
                "accessories_material"
            ]
            
            stock_data = self.res_to_dic(res, fields)
            
            # 为前端批量查询准备返回数据格式
            if product_ids and len(target_product_ids) > 1:
                # 构建以产品ID为键的字典，值为可用库存数量
                stock_dict = {}
                for item in stock_data:
                    product_id = item['product_id']
                    available_qty = item['available_qty']
                    # 如果同一个产品有多个库存记录，累加可用数量
                    if product_id in stock_dict:
                        stock_dict[product_id] += available_qty
                    else:
                        stock_dict[product_id] = available_qty
                
                # 确保所有查询的产品ID都有返回值
                for pid in target_product_ids:
                    if pid not in stock_dict:
                        stock_dict[pid] = 0
                
                cursor.close()
                conn.close()
                
                return jsonify({
                    'success': True,
                    'data': stock_dict,
                    'message': f'成功获取 {len(target_product_ids)} 个产品的库存信息'
                })
            else:
                # 单个产品查询保持原有格式
                cursor.close()
                conn.close()
                
                return jsonify({
                    'success': True,
                    'data': stock_data,
                    'message': f'成功获取产品 {target_product_ids[0]} 的库存信息，共 {len(stock_data)} 条记录'
                })
            
        except Exception as e:
            print(f"获取产品库存信息失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取产品库存信息失败: {str(e)}'
            }), 500

    
    @login_required
    def test_mobile_page(self):
        """测试移动版界面"""
        return render_template('/test_mobile.html')
    
    def get_ejj_jade_json_config(self):
        with open(os.path.join('./config','ejj_jade_config.json'),'r',encoding='utf-8') as f:
            config=json.load(f)
        return jsonify({'config':config})
    
    def upload_product_image(self):
        """上传产品图片，支持多选图片，自动调整分辨率"""
        try:
            # 获取上传的文件和产品ID
            uploaded_files = request.files.getlist('product_images')
            product_id = request.form.get('product_id')
            
            if not uploaded_files or not product_id:
                return jsonify({
                    'res': 'failed',
                    'message': '缺少图片文件或产品ID'
                }), 400
            
            # 获取图片目录配置
            with open(r'./config/ejj_jade.config','r',encoding='utf-8') as f:
                config = json.load(f)
            
            jade_dir = config['jade_dir']
            product_dir = os.path.join(jade_dir, product_id)
            
            # 确保产品目录存在
            os.makedirs(product_dir, exist_ok=True)
            
            processed_files = []
            
            for i, uploaded_file in enumerate(uploaded_files):
                # 检查文件格式
                if not uploaded_file.filename.lower().endswith(('.jpg', '.jpeg', '.png')):
                    continue
                
                # 处理图片格式转换和尺寸调整
                img = Image.open(uploaded_file.stream)
                
                # 转换为RGB模式（处理PNG透明背景）
                if img.mode in ('RGBA', 'LA'):
                    # 创建白色背景
                    background = Image.new('RGB', img.size, (255, 255, 255))
                    background.paste(img, mask=img.split()[-1])  # 使用alpha通道作为mask
                    img = background
                elif img.mode != 'RGB':
                    img = img.convert('RGB')
                
                # 计算目标尺寸
                original_width, original_height = img.size
                target_width, target_height = 600, 800
                
                # 计算缩放比例
                width_ratio = target_width / original_width
                height_ratio = target_height / original_height
                scale_ratio = min(width_ratio, height_ratio)
                
                # 计算新尺寸
                new_width = int(original_width * scale_ratio)
                new_height = int(original_height * scale_ratio)
                
                # 调整图片大小
                img = img.resize((new_width, new_height), Image.LANCZOS)
                
                # 创建白色背景画布
                final_img = Image.new('RGB', (target_width, target_height), (255, 255, 255))
                
                # 计算居中位置
                x_offset = (target_width - new_width) // 2
                y_offset = (target_height - new_height) // 2
                
                # 将调整后的图片粘贴到画布上
                final_img.paste(img, (x_offset, y_offset))
                
                # 生成文件名
                timestamp = datetime.now().strftime('%Y%m%d%H%M%S')
                filename = f'{product_id}-{timestamp}-{i+1}.jpg'
                filepath = os.path.join(product_dir, filename)
                
                # 保存为JPG格式
                final_img.save(filepath, 'JPEG', quality=95)
                processed_files.append(filename)
            
            return jsonify({
                'res': 'ok',
                'message': f'成功上传 {len(processed_files)} 张图片',
                'processed_files': processed_files,
                'product_id': product_id
            })
            
        except Exception as e:
            print(f"上传产品图片失败: {str(e)}")
            return jsonify({
                'res': 'failed',
                'message': f'上传图片失败: {str(e)}'
            }), 500
    
    def search_by_buyer(self):
        """按购买人搜索产品信息，包含库存信息"""
        try:
            buyer_name = request.args.get('buyer_name')
            if not buyer_name:
                return jsonify({
                    'success': False,
                    'message': '缺少购买人姓名参数'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()

            # 1. 从sales_table中查找匹配的stock_id
            sales_sql = """
                SELECT DISTINCT stock_id 
                FROM sales_table 
                WHERE buyer_name LIKE %s OR comment LIKE %s
            """
            cursor.execute(sales_sql, (f'%{buyer_name}%', f'%{buyer_name}%'))
            stock_ids = [row[0] for row in cursor.fetchall() if row[0]]

            if not stock_ids:
                cursor.close()
                conn.close()
                return jsonify({
                    'success': True,
                    'data': [],
                    'message': f'未找到购买人"{buyer_name}"相关的销售记录'
                })

            # 2. 从stock_table中查找对应的product_id
            stock_sql = """
                SELECT DISTINCT product_id 
                FROM stock_table 
                WHERE stock_id IN (%s)
            """ % ','.join(['%s'] * len(stock_ids))
            
            cursor.execute(stock_sql, stock_ids)
            product_ids = [row[0] for row in cursor.fetchall() if row[0]]

            if not product_ids:
                cursor.close()
                conn.close()
                return jsonify({
                    'success': True,
                    'data': [],
                    'message': f'未找到对应的产品信息'
                })

            # 3. 从product_table中获取产品详细信息
            product_sql = """
                SELECT 
                    product_id, product_name, wu_xings, colors,
                    major_material, second_material, third_material, accessories_material,
                    major_diameter, second_diameter, third_diameter,
                    type_1, type_2, adjust_length, adjust_shape, total_length,
                    pic_urls_post_dir, designer_id, designer_name, designed_date, comment,
                    created_at, updated_at
                FROM product_table 
                WHERE product_id IN (%s)
            """ % ','.join(['%s'] * len(product_ids))
            
            cursor.execute(product_sql, product_ids)
            res = cursor.fetchall()
            
            fields = [
                "product_id", "product_name", "wu_xings", "colors",
                "major_material", "second_material", "third_material", "accessories_material",
                "major_diameter", "second_diameter", "third_diameter",
                "type_1", "type_2", "adjust_length", "adjust_shape", "total_length",
                "pic_urls_post_dir", "designer_id", "designer_name", "designed_date", "comment",
                "created_at", "updated_at"
            ]
            
            products_data = self.res_to_dic(res, fields)
            
            # 4. 为每个产品查询库存信息
            for product in products_data:
                product_id = product['product_id']
                # 查询该产品的库存信息
                stock_sql = """
                    SELECT 
                        s.stock_id,
                        s.product_id,
                        s.qty as stock_qty,
                        COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0) as total_sale_qty,
                        (s.qty - COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0)) as available_qty
                    FROM stock_table s
                    LEFT JOIN sales_table sa ON s.stock_id = sa.stock_id
                    WHERE s.product_id = %s
                    GROUP BY s.stock_id, s.product_id, s.qty
                    HAVING (s.qty - COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0)) > 0
                """
                cursor.execute(stock_sql, (product_id,))
                stock_records = cursor.fetchall()
                
                # 计算总可用库存
                total_available_qty = sum(float(record[4] or 0) for record in stock_records)
                product['available_qty'] = total_available_qty
            
            cursor.close()
            conn.close()

            return jsonify({
                'success': True,
                'data': products_data,
                'message': f'找到 {len(products_data)} 个相关产品'
            })
            
        except Exception as e:
            print(f"按购买人搜索失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'搜索失败: {str(e)}'
            }), 500

    def search_product_ids(self):
        """模糊查询产品ID，从product_table和product_culture_table中搜索"""
        try:
            keyword = request.args.get('keyword', '')
            if not keyword:
                return jsonify({
                    'success': False,
                    'message': '请输入搜索关键词'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()

            # 从product_table中搜索
            product_sql = """
                SELECT product_id, product_name, type_1, designer_name, designed_date
                FROM product_table 
                WHERE product_id LIKE %s OR product_name LIKE %s
                ORDER BY created_at DESC
                LIMIT 20
            """
            cursor.execute(product_sql, (f'%{keyword}%', f'%{keyword}%'))
            product_results = cursor.fetchall()

            # 从product_culture_table中搜索
            culture_sql = """
                SELECT product_id, product_name, type, designer_name, designed_date
                FROM product_culture_table 
                WHERE product_id LIKE %s OR product_name LIKE %s
                ORDER BY created_at DESC
                LIMIT 20
            """
            cursor.execute(culture_sql, (f'%{keyword}%', f'%{keyword}%'))
            culture_results = cursor.fetchall()

            cursor.close()
            conn.close()

            # 处理结果
            results = []
            
            # 处理饰品产品结果
            for row in product_results:
                results.append({
                    'product_id': row[0],
                    'product_name': row[1],
                    'type': row[2] or '饰品',
                    'designer_name': row[3],
                    'designed_date': row[4],
                    'product_type': 'jade'  # 标识为饰品产品
                })
            
            # 处理文创产品结果
            for row in culture_results:
                results.append({
                    'product_id': row[0],
                    'product_name': row[1],
                    'type': row[2] or '文创',
                    'designer_name': row[3],
                    'designed_date': row[4],
                    'product_type': 'culture'  # 标识为文创产品
                })

            return jsonify({
                'success': True,
                'data': results,
                'message': f'找到 {len(results)} 个相关产品'
            })
            
        except Exception as e:
            print(f"搜索产品ID失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'搜索失败: {str(e)}'
            }), 500

    def get_product_details(self):
        """根据产品ID获取产品详细信息，包括图片路径"""
        try:
            product_id = request.args.get('product_id')
            if not product_id:
                return jsonify({
                    'success': False,
                    'message': '缺少产品ID参数'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()

            # 首先尝试从product_table中查找
            product_sql = """
                SELECT 
                    product_id, product_name, type_1, type_2,
                    designer_id, designer_name, designed_date, comment,
                    pic_urls_post_dir, wu_xings, colors,
                    major_material, second_material, third_material, accessories_material
                FROM product_table 
                WHERE product_id = %s
            """
            cursor.execute(product_sql, (product_id,))
            product_result = cursor.fetchone()

            product_type = 'jade'
            
            if not product_result:
                # 如果在product_table中没找到，尝试从product_culture_table中查找
                culture_sql = """
                    SELECT 
                        product_id, product_name, type,
                        designer_id, designer_name, designed_date, comment,
                        product_img_url, wu_xings, colors, materials
                    FROM product_culture_table 
                    WHERE product_id = %s
                """
                cursor.execute(culture_sql, (product_id,))
                product_result = cursor.fetchone()
                product_type = 'culture'

            if not product_result:
                cursor.close()
                conn.close()
                return jsonify({
                    'success': False,
                    'message': f'未找到产品ID为 {product_id} 的产品'
                }), 404

            # 根据产品类型处理结果
            if product_type == 'jade':
                fields = [
                    "product_id", "product_name", "type_1", "type_2",
                    "designer_id", "designer_name", "designed_date", "comment",
                    "pic_urls_post_dir", "wu_xings", "colors",
                    "major_material", "second_material", "third_material", "accessories_material"
                ]
                product_data = dict(zip(fields, product_result))
                product_data['product_type'] = 'jade'
            else:
                fields = [
                    "product_id", "product_name", "type",
                    "designer_id", "designer_name", "designed_date", "comment",
                    "product_img_url", "wu_xings", "colors", "materials"
                ]
                product_data = dict(zip(fields, product_result))
                product_data['product_type'] = 'culture'
                product_data['type_1'] = product_data['type']
                product_data['type_2'] = None

            # 获取图片目录配置
            with open(r'./config/ejj_jade.config','r',encoding='utf-8') as f:
                config = json.load(f)
            
            jade_dir = config.get('jade_dir', '')
            
            # 构建封面图片路径
            cover_image_path = None
            if product_type == 'jade' and jade_dir:
                cover_image = f'{product_id}-cover.jpg'
                cover_image_path = os.path.join(jade_dir, product_id, cover_image)
                if not os.path.exists(cover_image_path):
                    cover_image_path = None
            
            product_data['cover_image_path'] = cover_image_path
            product_data['cover_image_url'] = f'/serve_jade_image?product_id={product_id}&filename={product_id}-cover.jpg' if cover_image_path else None

            cursor.close()
            conn.close()

            print('product and culture data:',product_data)

            return jsonify({
                'success': True,
                'data': product_data,
                'message': '成功获取产品信息'
            })
            
        except Exception as e:
            print(f"获取产品详情失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取产品信息失败: {str(e)}'
            }), 500


    def deal_jade_cover_img(self):
        """处理玉石产品封面图片上传"""
        try:
            # 获取上传的文件和产品ID
            cover_image = request.files.get('cover_image')
            product_id = request.form.get('product_id')
            
            if not cover_image or not product_id:
                return jsonify({
                    'res': 'failed',
                    'message': '缺少封面图片或产品ID'
                }), 400
            
            # 获取图片目录配置
            with open(r'./config/ejj_jade.config','r',encoding='utf-8') as f:
                config = json.load(f)
            
            jade_dir = config['jade_dir']
            product_dir = os.path.join(jade_dir, product_id)
            
            # 确保产品目录存在
            os.makedirs(product_dir, exist_ok=True)
            
            # 处理图片格式转换和尺寸调整
            img = Image.open(cover_image.stream)
            
            # 转换为RGB模式（处理PNG透明背景）
            if img.mode in ('RGBA', 'LA'):
                # 创建白色背景
                background = Image.new('RGB', img.size, (255, 255, 255))
                background.paste(img, mask=img.split()[-1])  # 使用alpha通道作为mask
                img = background
            elif img.mode != 'RGB':
                img = img.convert('RGB')
            
            # 计算目标尺寸
            original_width, original_height = img.size
            target_width, target_height = 600, 800
            
            # 计算缩放比例
            width_ratio = target_width / original_width
            height_ratio = target_height / original_height
            scale_ratio = min(width_ratio, height_ratio)
            
            # 计算新尺寸
            new_width = int(original_width * scale_ratio)
            new_height = int(original_height * scale_ratio)
            
            # 调整图片大小
            img = img.resize((new_width, new_height), Image.LANCZOS)
            
            # 创建白色背景画布
            final_img = Image.new('RGB', (target_width, target_height), (255, 255, 255))
            
            # 计算居中位置
            x_offset = (target_width - new_width) // 2
            y_offset = (target_height - new_height) // 2
            
            # 将调整后的图片粘贴到画布上
            final_img.paste(img, (x_offset, y_offset))
            
            # 保存为JPG格式
            cover_filename = f'{product_id}-cover.jpg'
            cover_path = os.path.join(product_dir, cover_filename)
            final_img.save(cover_path, 'JPEG', quality=95)
            
            return jsonify({
                'res': 'ok',
                'message': '封面图片上传成功',
                'filename': cover_filename,
                'product_id': product_id
            })
            
        except Exception as e:
            print(f"处理封面图片失败: {str(e)}")
            return jsonify({
                'res': 'failed',
                'message': f'处理封面图片失败: {str(e)}'
            }), 500
    

    def get_culture_product_list(self):
        """获取文创产品列表"""
        try:
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 查询文创产品数据，基于 product_culture_table 字段
            sql = '''
            SELECT 
                id, product_id, type, wu_xings, colors, materials, size, 
                product_name, product_descript, product_img_url, 
                designer_id, designer_name, designer_company, designed_date, 
                comment, created_at, updated_at
            FROM product_culture_table
            ORDER BY created_at DESC
            '''
            
            cursor.execute(sql)
            res = cursor.fetchall()
            
            fields = [
                "id", "product_id", "type", "wu_xings", "colors", "materials", "size",
                "product_name", "product_descript", "product_img_url",
                "designer_id", "designer_name", "designer_company", "designed_date",
                "comment", "created_at", "updated_at"
            ]
            
            res_dic = self.res_to_dic(res, fields)
            cursor.close()
            conn.close()
            
            return jsonify({
                'res': 'ok',
                'data': res_dic,
                'message': f'成功获取 {len(res_dic)} 条文创产品记录'
            })
            
        except Exception as e:
            print(f"获取文创产品列表失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'res': 'failed',
                'message': f'获取文创产品数据失败: {str(e)}'
            }), 500


    def save_jade_product(self):
        """保存玉石产品信息到product_table"""
        with open(r'./config/ejj_jade.config','r',encoding='utf-8') as f:
            config=json.load(f)
        save_dir=config['jade_dir']

        try:
            data = request.json
            print("接收到的产品数据:", data)
            
            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 检查product_id是否已存在
            product_id = data.get('product_id')
            if product_id:
                check_sql = "SELECT COUNT(*) FROM product_table WHERE product_id = %s"
                cursor.execute(check_sql, (product_id,))
                product_exists = cursor.fetchone()[0]
                
                if product_exists > 0:
                    return jsonify({
                        'res': 'failed',
                        'message': f'产品ID {product_id} 已存在，请使用唯一的产品ID'
                    }), 400
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 构建插入SQL语句
            sql = """
                INSERT INTO product_table (
                    product_id, product_name, wu_xings, colors,
                    major_material, second_material, third_material, accessories_material,
                    major_diameter, second_diameter, third_diameter,
                    type_1, type_2,
                    adjust_length, adjust_shape, total_length,
                    pic_urls_post_dir,
                    designer_id, designer_name, designed_date, comment,
                    created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            # 准备插入值
            values = (
                data.get('product_id'),
                data.get('product_name'),
                data.get('wu_xings'),
                data.get('colors'),
                data.get('major_material'),
                data.get('second_material') or None,
                data.get('third_material') or None,
                data.get('accessories_material') or None,
                float(data.get('major_diameter', 0)) if data.get('major_diameter') else None,
                float(data.get('second_diameter', 0)) if data.get('second_diameter') else None,
                float(data.get('third_diameter', 0)) if data.get('third_diameter') else None,
                data.get('type_1'),
                data.get('type_2'),
                data.get('adjust_length', 'no'),
                data.get('adjust_shape', 'no'),
                float(data.get('total_length', 0)) if data.get('total_length') else None,
                data.get('pic_urls_post_dir') or None,
                data.get('designer_id'),
                data.get('designer_name'),
                data.get('designed_date'),
                data.get('comment', ''),
                current_time,
                current_time
            )
            
            # 执行插入
            cursor.execute(sql, values)
            
            # 提交事务
            conn.commit()
            cursor.close()
            conn.close()

            save_path=os.path.join(save_dir,data.get('product_id'))
            print(f'新建文件夹 {save_path}')
            os.makedirs(save_path,exist_ok=True)
            
            
            return jsonify({
                'res': 'ok',
                'message': '产品信息保存成功',
                'product_id': data.get('product_id')
            })
            
        except Exception as e:
            print(f"保存产品信息失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'res': 'failed',
                'message': f'保存失败: {str(e)}'
            }), 500

    def save_culture_product(self):
        """保存文创产品信息到product_culture_table"""
        try:
            data = request.json
            print("接收到的文创产品数据:", data)
            
            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 检查product_id是否已存在
            product_id = data.get('product_id')
            if product_id:
                check_sql = "SELECT COUNT(*) FROM product_culture_table WHERE product_id = %s"
                cursor.execute(check_sql, (product_id,))
                product_exists = cursor.fetchone()[0]
                
                if product_exists > 0:
                    return jsonify({
                        'res': 'failed',
                        'message': f'文创产品ID {product_id} 已存在，请使用唯一的产品ID'
                    }), 400
            
            # 处理设计师信息（从下拉菜单获取）
            designer_select = data.get('designer_select', '')
            designer_id = ''
            designer_name = ''
            designer_company = data.get('designer_company', '')  # 从表单获取公司名称
            
            if designer_select:
                # 分割设计师ID、姓名和公司（格式：DE001-水母-小E姐姐）
                designer_parts = designer_select.split('-')
                if len(designer_parts) >= 2:
                    designer_id = designer_parts[0]
                    designer_name = designer_parts[1]
                # 公司名称已经从表单获取，不需要从下拉菜单分割
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 构建插入SQL语句（移除product_img_url字段）
            sql = """
                INSERT INTO product_culture_table (
                    product_id, type, wu_xings, colors, materials, size,
                    product_name, product_descript,
                    designer_id, designer_name, designer_company, designed_date, comment,
                    created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            # 准备插入值
            values = (
                data.get('product_id'),
                data.get('type'),
                data.get('wu_xings'),
                data.get('colors'),
                data.get('materials'),
                data.get('size'),
                data.get('product_name'),
                data.get('product_descript'),
                designer_id,
                designer_name,
                data.get('designer_company'),
                data.get('designed_date'),
                data.get('comment', ''),
                current_time,
                current_time
            )
            
            # 执行插入
            cursor.execute(sql, values)
            
            # 提交事务
            conn.commit()
            cursor.close()
            conn.close()
            
            return jsonify({
                'res': 'ok',
                'message': '文创产品信息保存成功',
                'product_id': data.get('product_id')
            })
            
        except Exception as e:
            print(f"保存文创产品信息失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'res': 'failed',
                'message': f'保存失败: {str(e)}'
            }), 500
    
    def export_daily_xlsx(self):
        """导出按日+买家分类汇总数据到Excel文件"""
        try:
            # 获取查询参数
            start_date = request.args.get('start_date')
            end_date = request.args.get('end_date')
            
            if not start_date or not end_date:
                return jsonify({
                    'success': False,
                    'message': '缺少开始日期或结束日期参数'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 查询按日+买家分类汇总数据
            sql = """
                SELECT 
                    DATE(s.sale_datetime) as sale_date,
                    s.buyer_name,
                    SUM(s.sale_qty) as total_qty,
                    SUM(s.sale_real_price) as total_amount
                FROM sales_table s
                WHERE DATE(s.sale_datetime) BETWEEN %s AND %s
                GROUP BY DATE(s.sale_datetime), s.buyer_name
                ORDER BY sale_date DESC, total_amount DESC
            """
            cursor.execute(sql, (start_date, end_date))
            res = cursor.fetchall()
            
            fields = ["sale_date", "buyer_name", "total_qty", "total_amount"]
            data = self.res_to_dic(res, fields)
            
            # 计算汇总信息
            total_qty = sum(float(item['total_qty'] or 0) for item in data)
            total_amount = sum(float(item['total_amount'] or 0) for item in data)
            
            # 创建DataFrame
            df = pd.DataFrame(data)
            
            # 重命名列名为中文
            df.columns = ['销售日期', '买家姓名', '销售件数', '销售金额']
            
            # 创建Excel文件
            output = io.BytesIO()
            with pd.ExcelWriter(output, engine='openpyxl') as writer:
                # 写入详细数据
                df.to_excel(writer, sheet_name='销售汇总', index=False)
                
                # 写入汇总信息
                summary_data = {
                    '统计项目': ['统计期间', '总销售件数', '总销售金额'],
                    '数值': [f'{start_date} 至 {end_date}', total_qty, total_amount]
                }
                summary_df = pd.DataFrame(summary_data)
                summary_df.to_excel(writer, sheet_name='汇总信息', index=False)
                
                # 调整列宽
                for sheet_name in writer.sheets:
                    worksheet = writer.sheets[sheet_name]
                    for column in worksheet.columns:
                        max_length = 0
                        column_letter = column[0].column_letter
                        for cell in column:
                            try:
                                if len(str(cell.value)) > max_length:
                                    max_length = len(str(cell.value))
                            except:
                                pass
                        adjusted_width = min(max_length + 2, 30)
                        worksheet.column_dimensions[column_letter].width = adjusted_width
            
            output.seek(0)
            
            cursor.close()
            conn.close()
            
            # 生成文件名
            filename = f"销售汇总_{start_date}_至_{end_date}.xlsx"
            
            return send_file(
                output,
                as_attachment=True,
                download_name=filename,
                mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            
        except Exception as e:
            print(f"导出Excel文件失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'导出失败: {str(e)}'
            }), 500
    
    def get_sale_stat_data(self):
        """获取销售统计数据，支持按日+买家分类汇总和销售明细清单两种统计类型"""
        try:
            # 获取查询参数
            start_date = request.args.get('start_date')
            end_date = request.args.get('end_date')
            stat_type = request.args.get('stat_type', 'daily_buyer')
            
            if not start_date or not end_date:
                return jsonify({
                    'success': False,
                    'message': '缺少开始日期或结束日期参数'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            if stat_type == 'daily_buyer':
                # 按日+买家分类汇总
                sql = """
                    SELECT 
                        DATE(s.sale_datetime) as sale_date,
                        s.buyer_name,
                        SUM(s.sale_qty) as total_qty,
                        SUM(s.sale_real_price) as total_amount
                    FROM sales_table s
                    WHERE DATE(s.sale_datetime) BETWEEN %s AND %s
                    GROUP BY DATE(s.sale_datetime), s.buyer_name
                    ORDER BY sale_date DESC, total_amount DESC
                """
                cursor.execute(sql, (start_date, end_date))
                res = cursor.fetchall()
                
                fields = ["sale_date", "buyer_name", "total_qty", "total_amount"]
                data = self.res_to_dic(res, fields)
                
                # 计算汇总信息
                total_qty = sum(float(item['total_qty'] or 0) for item in data)
                total_amount = sum(float(item['total_amount'] or 0) for item in data)
                
                summary = {
                    'total_qty': total_qty,
                    'total_amount': total_amount,
                    'total_cost': 0,  # 按日汇总不计算成本
                    'total_profit': 0  # 按日汇总不计算利润
                }
                
            elif stat_type == 'detail_list':
                # 销售明细清单，包含成本计算
                sql = """
                    SELECT 
                        s.id,
                        s.sale_datetime,
                        s.stock_id,
                        s.sale_qty,
                        s.sale_real_price,
                        s.buyer_name,
                        s.buyer_company,
                        st.product_id,
                        -- 计算总成本：材料+手工+运费+其他
                        (COALESCE(st.material_price, 0) + 
                         COALESCE(st.manual_price, 0) + 
                         COALESCE(st.transfer_price, 0) + 
                         COALESCE(st.other_price, 0)) * s.sale_qty as total_cost
                    FROM sales_table s
                    LEFT JOIN stock_table st ON s.stock_id = st.stock_id
                    WHERE DATE(s.sale_datetime) BETWEEN %s AND %s
                    ORDER BY s.sale_datetime DESC
                """
                cursor.execute(sql, (start_date, end_date))
                res = cursor.fetchall()
                
                fields = [
                    "id", "sale_datetime", "stock_id", "sale_qty", 
                    "sale_real_price", "buyer_name", "buyer_company", 
                    "product_id", "total_cost"
                ]
                data = self.res_to_dic(res, fields)
                
                # 计算汇总信息
                total_qty = sum(float(item['sale_qty'] or 0) for item in data)
                total_amount = sum(float(item['sale_real_price'] or 0) for item in data)
                total_cost = sum(float(item['total_cost'] or 0) for item in data)
                total_profit = total_amount - total_cost
                
                summary = {
                    'total_qty': total_qty,
                    'total_amount': total_amount,
                    'total_cost': total_cost,
                    'total_profit': total_profit
                }
            else:
                return jsonify({
                    'success': False,
                    'message': '不支持的统计类型'
                }), 400
            
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'data': data,
                'summary': summary,
                'message': f'成功获取 {len(data)} 条统计记录'
            })
            
        except Exception as e:
            print(f"获取销售统计数据失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取统计数据失败: {str(e)}'
            }), 500
    
    def update_sale_settlement(self):
        """更新销售结算信息，将结算数据写入sales_table"""
        try:
            data = request.json
            print("接收到的结算数据:", data)
            
            # 验证必填字段
            if not data.get('sale_id'):
                return jsonify({
                    'success': False,
                    'message': '缺少销售ID'
                }), 400
                
            if not data.get('settle_amount'):
                return jsonify({
                    'success': False,
                    'message': '缺少结算金额'
                }), 400
                
            if not data.get('settle_payer'):
                return jsonify({
                    'success': False,
                    'message': '缺少支付人'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 获取销售记录信息
            sale_sql = """
                SELECT sale_total_price, settled_price, settlement_status 
                FROM sales_table 
                WHERE id = %s
            """
            cursor.execute(sale_sql, (data['sale_id'],))
            sale_info = cursor.fetchone()
            
            if not sale_info:
                return jsonify({
                    'success': False,
                    'message': '未找到对应的销售记录'
                }), 404
            
            sale_total_price = float(sale_info[0] or 0)
            current_settled_price = float(sale_info[1] or 0)
            current_status = sale_info[2]
            
            # 计算新的结算金额和状态
            settle_amount = float(data['settle_amount'])
            new_settled_price = current_settled_price + settle_amount
            
            # 确定结算状态
            if new_settled_price >= sale_total_price:
                settlement_status = 'completed'  # 已结算完毕
            elif new_settled_price > 0:
                settlement_status = 'partial'    # 部分结算
            else:
                settlement_status = 'pending'    # 未结算
            
            # 处理结算时间
            settle_datetime = data.get('settle_datetime')
            if not settle_datetime:
                settle_datetime = current_time
            else:
                # 将前端传来的datetime-local格式转换为数据库格式
                settle_datetime = settle_datetime.replace('T', ' ') + ':00'
            
            # 构建更新SQL
            update_sql = """
                UPDATE sales_table 
                SET settled_price = %s,
                    settlement_status = %s,
                    settled_datetime = %s,
                    settle_payer = %s,
                    comment = CONCAT(IFNULL(comment, ''), %s),
                    updated_at = %s
                WHERE id = %s
            """
            
            # 准备更新值
            comment_addition = f"\n{current_time}: 结算¥{settle_amount} - 支付人: {data['settle_payer']}"
            if data.get('settle_comment'):
                comment_addition += f" - {data['settle_comment']}"
            
            update_values = (
                new_settled_price,
                settlement_status,
                settle_datetime,
                data['settle_payer'],
                comment_addition,
                current_time,
                data['sale_id']
            )
            
            # 执行更新
            cursor.execute(update_sql, update_values)
            
            # 提交事务
            conn.commit()
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': '结算信息更新成功',
                'data': {
                    'new_settled_price': new_settled_price,
                    'settlement_status': settlement_status,
                    'settle_datetime': settle_datetime
                }
            })
            
        except Exception as e:
            print(f"更新销售结算信息失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'更新失败: {str(e)}'
            }), 500
    
    def batch_update_sale_settlement(self):
        """批量更新销售结算信息"""
        try:
            data = request.json
            print("接收到的批量结算数据:", data)
            
            sale_settlements = data.get('sale_settlements', [])
            settle_payer = data.get('settle_payer', '晓')
            settle_datetime = data.get('settle_datetime')
            comment = data.get('comment', '')
            
            if not sale_settlements:
                return jsonify({
                    'success': False,
                    'message': '缺少销售结算数据'
                }), 400

            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            settled_count = 0
            
            # 对每个销售结算记录处理
            for settlement in sale_settlements:
                sale_id = settlement.get('sale_id')
                settle_amount = float(settlement.get('settle_amount', 0))
                
                if not sale_id or settle_amount <= 0:
                    continue
                
                # 获取销售记录信息
                sale_sql = """
                    SELECT sale_total_price, settled_price, settlement_status 
                    FROM sales_table 
                    WHERE id = %s
                """
                cursor.execute(sale_sql, (sale_id,))
                sale_info = cursor.fetchone()
                
                if not sale_info:
                    continue
                
                sale_total_price = float(sale_info[0] or 0)
                current_settled_price = float(sale_info[1] or 0)
                current_status = sale_info[2]
                
                # 计算新的结算金额和状态
                new_settled_price = current_settled_price + settle_amount
                
                # 确定结算状态
                if new_settled_price >= sale_total_price:
                    settlement_status = 'completed'  # 已结算完毕
                elif new_settled_price > 0:
                    settlement_status = 'partial'    # 部分结算
                else:
                    settlement_status = 'pending'    # 未结算
                
                # 处理结算时间
                if not settle_datetime:
                    settle_datetime_str = current_time
                else:
                    # 将前端传来的datetime-local格式转换为数据库格式
                    settle_datetime_str = settle_datetime.replace('T', ' ') + ':00'
                
                # 构建更新SQL
                update_sql = """
                    UPDATE sales_table 
                    SET settled_price = %s,
                        settlement_status = %s,
                        settled_datetime = %s,
                        settle_payer = %s,
                        comment = CONCAT(IFNULL(comment, ''), %s),
                        updated_at = %s
                    WHERE id = %s
                """
                
                # 准备更新值
                comment_addition = f"\n{current_time}: 批量结算支付{settle_payer} ¥{settle_amount:.2f}"
                if comment:
                    comment_addition += f" - {comment}"
                
                update_values = (
                    new_settled_price,
                    settlement_status,
                    settle_datetime_str,
                    settle_payer,
                    comment_addition,
                    current_time,
                    sale_id
                )
                
                cursor.execute(update_sql, update_values)
                settled_count += 1
            
            conn.commit()
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': f'批量结算成功，共处理 {settled_count} 条销售记录',
                'settled_count': settled_count
            })
            
        except Exception as e:
            print(f"批量结算失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'批量结算失败: {str(e)}'
            }), 500
    
    def get_sale_settle_data(self):
        """获取销售结算数据，从sales_table中获取所有销售记录"""
        try:
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 查询销售数据，包含所有字段，并连接stock_table获取product_id
            sql = """
                SELECT 
                    s.id, s.sale_datetime, s.buyer_id, s.buyer_name, s.buyer_company, 
                    s.stock_id, st.product_id, s.sale_price, s.sale_qty, s.sale_discount, 
                    s.sale_real_price, s.sale_total_price, s.saler_id, s.saler_name, 
                    s.settlement_status, s.settled_price, s.settled_datetime, 
                    s.settle_payer, s.comment, s.created_at, s.updated_at
                FROM sales_table s
                LEFT JOIN stock_table st ON s.stock_id = st.stock_id
                ORDER BY s.sale_datetime DESC
            """
            
            cursor.execute(sql)
            res = cursor.fetchall()
            
            fields = [
                "id", "sale_datetime", "buyer_id", "buyer_name", "buyer_company",
                "stock_id", "product_id", "sale_price", "sale_qty", "sale_discount", 
                "sale_real_price", "sale_total_price", "saler_id", "salername", 
                "settlement_status", "settled_price", "settled_datetime", 
                "settle_payer", "comment", "created_at", "updated_at"
            ]
            
            res_dic = self.res_to_dic(res, fields)
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'data': res_dic,
                'message': f'成功获取 {len(res_dic)} 条销售记录'
            })
            
        except Exception as e:
            print(f"获取销售结算数据失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取销售数据失败: {str(e)}'
            }), 500
    
    def get_settlement_data(self):
        """获取结算数据，连接product_table, settlement_stock_table, stock_table"""
        try:
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 查询结算数据，连接三个表 - 使用新的字段结构
            sql = """
                SELECT 
                    s.stock_id,
                    s.stock_name,
                    s.product_id,
                    s.product_name,
                    s.type_1,
                    s.type_2,
                    s.material_price,
                    s.manual_price,
                    s.transfer_price,
                    s.other_price,
                    s.qty,
                    s.purchase_date,
                    ss.settlement_type,
                    ss.settlement_status,
                    ss.amount,
                    ss.settled_amount,
                    ss.balance_amount,
                    ss.advance_payer,
                    ss.settlement_payer,
                    ss.advance_date,
                    ss.settlement_date,
                    ss.comment as settlement_comment,
                    p.pic_urls_post_dir,
                    p.wu_xings,
                    p.colors,
                    p.major_material,
                    p.second_material,
                    p.third_material,
                    p.accessories_material,
                    p.major_diameter,
                    p.second_diameter,
                    p.third_diameter,
                    p.adjust_length,
                    p.adjust_shape,
                    p.total_length,
                    p.designer_id,
                    p.designer_name,
                    p.designed_date,
                    p.comment as product_comment
                FROM stock_table s
                LEFT JOIN settlement_stock_table ss ON s.stock_id = ss.stock_id
                LEFT JOIN product_table p ON s.product_id = p.product_id
                ORDER BY s.created_at DESC, ss.settlement_type
            """
            
            cursor.execute(sql)
            res = cursor.fetchall()
            
            fields = [
                "stock_id", "stock_name", "product_id", "product_name",
                "type_1", "type_2", "material_price", "manual_price", 
                "transfer_price", "other_price", "qty", "purchase_date",
                "settlement_type", "settlement_status", "amount", 
                "settled_amount", "balance_amount", "advance_payer",
                "settlement_payer", "advance_date", "settlement_date", "settlement_comment", 
                "pic_urls_post_dir", "wu_xings", "colors", "major_material", 
                "second_material", "third_material", "accessories_material",
                "major_diameter", "second_diameter", "third_diameter",
                "adjust_length", "adjust_shape", "total_length",
                "designer_id", "designer_name", "designed_date", "product_comment"
            ]
            
            res_dic = self.res_to_dic(res, fields)
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'data': res_dic
            })
            
        except Exception as e:
            print(f"获取结算数据失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取结算数据失败: {str(e)}'
            }), 500

    def update_settlement_stock(self):
        """更新结算信息"""
        try:
            data = request.json
            print("接收到的结算更新数据:", data)
            
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 根据结算类型更新对应的字段
            settlement_type = data.get('settlement_type')
            stock_id = data.get('stock_id')
            payment_amount = float(data.get('payment_amount', 0))
            payer = data.get('payer')
            comment = data.get('comment', '')
            
            # 构建更新SQL - 使用新的字段结构，基于初始余额计算
            update_sql = """
                UPDATE settlement_stock_table 
                SET settlement_status = CASE 
                    WHEN settled_amount + %s >= amount THEN 'completed'
                    WHEN settled_amount + %s > 0 THEN 'partial'
                    ELSE 'pending'
                END,
                settled_amount = settled_amount + %s,
                balance_amount = %s - %s,
                settlement_payer = %s,
                settlement_date = %s,
                comment = CONCAT(IFNULL(comment, ''), %s),
                updated_at = %s
                WHERE stock_id = %s AND settlement_type = %s
            """
            
            # 使用初始余额减去支付金额来计算新的余额
            initial_balance = float(data.get('initial_balance_amount', 0))
            update_values = [
                payment_amount, payment_amount, payment_amount,
                initial_balance, payment_amount,
                payer, current_time, 
                f"\n{current_time}: 支付{payer} ¥{payment_amount} - {comment}",
                current_time, stock_id, settlement_type
            ]
            
            cursor.execute(update_sql, update_values)
            
            # 检查是否更新成功
            if cursor.rowcount == 0:
                # 如果没有找到匹配的记录，可能需要插入新记录
                insert_sql = """
                    INSERT INTO settlement_stock_table (
                        stock_id, settlement_type, settlement_status, amount, 
                        settled_amount, balance_amount, advance_payer, settlement_payer, 
                        advance_date, settlement_date, comment, created_at, updated_at
                    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                
                # 获取库存信息来计算金额
                stock_sql = "SELECT material_price, manual_price, transfer_price, other_price, qty FROM stock_table WHERE stock_id = %s"
                cursor.execute(stock_sql, (stock_id,))
                stock_info = cursor.fetchone()
                
                if stock_info:
                    material_price, manual_price, transfer_price, other_price, qty = stock_info
                    
                    # 根据结算类型计算金额
                    if settlement_type == 'material':
                        amount = float(material_price or 0) * float(qty or 0)
                    elif settlement_type == 'manual':
                        amount = float(manual_price or 0) * float(qty or 0)
                    elif settlement_type == 'transfer':
                        amount = float(transfer_price or 0) * float(qty or 0)
                    elif settlement_type == 'other':
                        amount = float(other_price or 0) * float(qty or 0)
                    else:
                        amount = 0
                    
                    # 设置结算状态
                    if payment_amount >= amount:
                        settlement_status = 'completed'
                    elif payment_amount > 0:
                        settlement_status = 'partial'
                    else:
                        settlement_status = 'pending'
                    
                    insert_values = (
                        stock_id, settlement_type, settlement_status,
                        amount, payment_amount, amount - payment_amount,
                        payer, payer, current_time, current_time, comment,
                        current_time, current_time
                    )
                    
                    cursor.execute(insert_sql, insert_values)
            
            conn.commit()
            cursor.close()
            conn.close()

            print('结算信息更新成功')
            
            return jsonify({
                'success': True,
                'message': '结算信息更新成功'
            })
            
        except Exception as e:
            print(f"更新结算信息失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'更新失败: {str(e)}'
            }), 500
    
    def get_jade_config(self):
        """获取玉石配置信息"""
        try:
            with open(os.path.join('./config','ejj_jade.config'),'r',encoding='utf-8') as f:
                config=json.load(f)
            
            return jsonify({'res': 'ok', 'config': config})
        except Exception as e:
            print(f'获取玉石配置失败: {str(e)}')
            return jsonify({'res': 'failed', 'msg': f'获取配置失败: {str(e)}'}), 500

    def get_jade_images(self):
        """获取指定产品ID的所有图片列表"""
        try:
            product_id = request.args.get('product_id')
            if not product_id:
                return jsonify({'res': 'failed', 'msg': '缺少产品ID参数'}), 400
            
            # 获取图片目录配置
            config_path = os.path.join('./config','ejj_jade.config')
            if not os.path.exists(config_path):
                return jsonify({'res': 'failed', 'msg': '配置文件不存在'}), 500
                
            with open(config_path, 'r', encoding='utf-8') as f:
                config = json.load(f)
            
            jade_dir = config.get('jade_dir', '')
            if not jade_dir:
                return jsonify({'res': 'failed', 'msg': '未配置图片目录'}), 500
            
            # 构建产品图片目录路径
            product_dir = os.path.join(jade_dir, product_id)
            
            # 检查目录是否存在
            if not os.path.exists(product_dir):
                return jsonify({'res': 'failed', 'msg': f'产品ID {product_id} 的图片目录不存在'}), 404
            
            # 获取目录中的所有图片文件
            image_files = []
            try:
                for filename in os.listdir(product_dir):
                    if filename.lower().endswith(('.jpg', '.jpeg', '.png', '.gif', '.bmp', '.webp')):
                        image_files.append(filename)
            except PermissionError:
                return jsonify({'res': 'failed', 'msg': '没有权限访问图片目录'}), 403
            except Exception as e:
                return jsonify({'res': 'failed', 'msg': f'读取目录失败: {str(e)}'}), 500
            
            # 按文件名排序
            image_files.sort()
            
            return jsonify({
                'res': 'ok',
                'images': image_files,
                'product_id': product_id
            })
            
        except json.JSONDecodeError as e:
            print(f'配置文件格式错误: {str(e)}')
            return jsonify({'res': 'failed', 'msg': '配置文件格式错误'}), 500
        except Exception as e:
            print(f'获取产品图片列表失败: {str(e)}')
            return jsonify({'res': 'failed', 'msg': f'获取图片列表失败: {str(e)}'}), 500

        

    def get_jade_product_list(self):
        conn=self.connect_mysql_ej()
        cursor=conn.cursor()
        sql='''
        select product_id, product_name, wu_xings, colors,
                major_material, second_material, third_material, accessories_material,
                major_diameter, second_diameter, third_diameter,
                type_1, type_2,
                adjust_length, adjust_shape, total_length,
                pic_urls_post_dir,
                designer_id, designer_name, designed_date, comment,
                created_at, updated_at
        from product_table;

        '''
        cursor.execute(sql)
        res=cursor.fetchall()
        fields = [
            "product_id",
            "product_name",
            "wu_xings",
            "colors",
            "major_material",
            "second_material",
            "third_material",
            "accessories_material",
            "major_diameter",
            "second_diameter",
            "third_diameter",
            "type_1",
            "type_2",
            "adjust_length",
            "adjust_shape",
            "total_length",
            "pic_urls_post_dir",
            "designer_id",
            "designer_name",
            "designed_date",
            "comment",
            "created_at",
            "updated_at"
        ]
        
        res_dic=self.res_to_dic(res,fields)
        return jsonify({'res':'ok','data':res_dic})
        
    def get_valid_stock_ids(self):
        """获取有效库存编号 - stock_table中的库存数量减去sales_table中的销售数量，大于0的为有效库存"""
        try:
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 查询有效库存编号 - 只统计未退单的销售记录 (is_returned = 0)，包含成本字段和产品信息
            sql = """
                SELECT 
                    s.stock_id,
                    s.product_id,
                    s.qty as stock_qty,
                    COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0) as total_sale_qty,
                    (s.qty - COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0)) as available_qty,
                    s.material_price,
                    s.manual_price,
                    s.transfer_price,
                    s.other_price,
                    p.wu_xings,
                    p.colors,
                    p.major_material
                FROM stock_table s
                LEFT JOIN sales_table sa ON s.stock_id = sa.stock_id
                LEFT JOIN product_table p ON s.product_id = p.product_id
                GROUP BY s.stock_id, s.product_id, s.qty, s.material_price, s.manual_price, s.transfer_price, s.other_price, p.wu_xings, p.colors, p.major_material
                HAVING (s.qty - COALESCE(SUM(CASE WHEN sa.is_returned = 0 THEN sa.sale_qty ELSE 0 END), 0)) > 0
                ORDER BY s.stock_id
            """
            
            cursor.execute(sql)
            res = cursor.fetchall()
            
            fields = [
                "stock_id",
                "product_id", 
                "stock_qty",
                "total_sale_qty",
                "available_qty",
                "material_price",
                "manual_price",
                "transfer_price",
                "other_price",
                "wu_xings",
                "colors",
                "major_material"
            ]
            
            res_dic = self.res_to_dic(res, fields)
            cursor.close()
            conn.close()
            
            return jsonify({
                'res': 'ok',
                'data': res_dic,
                'message': f'找到 {len(res_dic)} 个有效库存编号'
            })
            
        except Exception as e:
            print(f"获取有效库存编号失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'res': 'failed',
                'message': f'获取有效库存编号失败: {str(e)}'
            }), 500

    def get_export_stock_data(self):
        """获取导出库存数据 - 包含成本信息和产品详细信息"""
        try:
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 查询有效库存数据，包含成本信息和产品详细信息
            sql = """
                SELECT 
                    s.stock_id,
                    s.product_id,
                    s.qty as stock_qty,
                    COALESCE(SUM(sa.sale_qty), 0) as total_sale_qty,
                    (s.qty - COALESCE(SUM(sa.sale_qty), 0)) as available_qty,
                    s.material_price,
                    s.manual_price, 
                    s.transfer_price,
                    s.other_price,
                    p.product_name,
                    p.type_1,
                    p.type_2,
                    p.wu_xings,
                    p.colors,
                    p.major_material,
                    p.second_material,
                    p.third_material,
                    p.accessories_material,
                    p.pic_urls_post_dir
                FROM stock_table s
                LEFT JOIN sales_table sa ON s.stock_id = sa.stock_id
                LEFT JOIN product_table p ON s.product_id = p.product_id
                GROUP BY s.stock_id, s.product_id, s.qty
                HAVING (s.qty - COALESCE(SUM(sa.sale_qty), 0)) > 0
                ORDER BY s.stock_id
            """
            
            cursor.execute(sql)
            res = cursor.fetchall()
            
            fields = [
                "stock_id",
                "product_id", 
                "stock_qty",
                "total_sale_qty",
                "available_qty",
                "material_price",
                "manual_price",
                "transfer_price",
                "other_price",
                "product_name",
                "type_1",
                "type_2",
                "wu_xings",
                "colors",
                "major_material",
                "second_material",
                "third_material",
                "accessories_material",
                "pic_urls_post_dir"
            ]
            
            res_dic = self.res_to_dic(res, fields)
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'data': res_dic,
                'message': f'成功获取 {len(res_dic)} 条库存导出数据'
            })
            
        except Exception as e:
            print(f"获取库存导出数据失败: {str(e)}")
            if 'conn' in locals():
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'获取库存导出数据失败: {str(e)}'
            }), 500

    def export_sale_excel(self):
        """导出销售列表Excel文件，包含产品图片"""
        try:
            data = request.json
            stock_data = data.get('stock_data', [])

            print('stock_data:',stock_data)

            if not stock_data:
                return jsonify({
                    'success': False,
                    'message': '没有数据可导出'
                }), 400

            # 创建工作簿
            wb = openpyxl.Workbook()
            ws = wb.active
            ws.title = "销售列表"

            # 设置表头
            headers = [
                "序号", "库存编号", "产品图片", "类别", "五行属性", "材料",
                "数量", "成本价","日期"
            ]
            
            for col_num, header in enumerate(headers, 1):
                ws.cell(row=1, column=col_num, value=header)

            # 填充数据
            for row_num, stock in enumerate(stock_data, 2):
                # 合并类别
                # category = f"{stock.get('type_1', '')}-{stock.get('type_2', '')}".strip('-')
                
                
                # 合并所有材料
                # materials = [
                #     stock.get('major_material', ''),
                #     stock.get('second_material', ''),
                #     stock.get('third_material', ''),
                #     stock.get('accessories_material', '')
                # ]
                # materials_str = '，'.join([m for m in materials if m])
                materials_str=stock.get('material','')

                # 计算成本合计
                material_price = float(stock.get('material_price', 0) or 0)
                manual_price = float(stock.get('manual_price', 0) or 0)
                transfer_price = float(stock.get('transfer_price', 0) or 0)
                other_price = float(stock.get('other_price', 0) or 0)
                total_cost = material_price + manual_price + transfer_price + other_price
                
                # 计算成本价
                available_qty = float(stock.get('available_qty', 0) or 0)
                cost_price = (total_cost + 40) * available_qty

                # 写入数据
                ws.cell(row=row_num, column=1, value=row_num - 1)  # 序号
                ws.cell(row=row_num, column=2, value=stock.get('stock_id', ''))  # 库存编号
                ws.cell(row=row_num, column=3, value="图片")  # 图片列占位符
                ws.cell(row=row_num, column=4, value=stock.get('category',''))  # 类别
                ws.cell(row=row_num, column=5, value=stock.get('wuxing', ''))  # 五行属性
                ws.cell(row=row_num, column=6, value=materials_str)  # 材料
                # ws.cell(row=row_num, column=7, value=material_price)  # 材料成本
                # ws.cell(row=row_num, column=8, value=manual_price)  # 手工成本
                # ws.cell(row=row_num, column=9, value=transfer_price)  # 运输成本
                # ws.cell(row=row_num, column=10, value=other_price)  # 其他成本
                # ws.cell(row=row_num, column=11, value=total_cost)  # 成本合计
                ws.cell(row=row_num, column=7, value=available_qty)  # 数量
                ws.cell(row=row_num, column=8, value=cost_price)  # 成本价
                ws.cell(row=row_num, column=9, value='')  # 成本价

                # 处理产品图片
                product_id = stock.get('product_id')
                if product_id:
                    try:
                        # 获取产品图片
                        image_url = f"/serve_jade_image?product_id={product_id}"
                        # 读取正确的jade_dir配置
                        with open(os.path.join('./config','ejj_jade.config'),'r',encoding='utf-8') as f:
                            jade_config = json.load(f)
                        image_path = os.path.join(jade_config['jade_dir'], product_id, f"{product_id}-cover.jpg")
                        
                        if os.path.exists(image_path):
                            # 插入图片到Excel
                            img = openpyxl.drawing.image.Image(image_path)
                            img.width = 60
                            img.height = 60
                            img.anchor = f'C{row_num}'  # 图片列
                            ws.add_image(img)
                            # 调整行高以适应图片
                            ws.row_dimensions[row_num].height = 60
                            # 移除占位符文本，只显示图片
                            ws.cell(row=row_num, column=3, value="")
                        else:
                            print(f"图片文件不存在: {image_path}")
                    except Exception as e:
                        print(f"插入图片失败: {product_id} - {str(e)}")

            # 调整列宽
            for column in ws.columns:
                max_length = 0
                column_letter = column[0].column_letter
                for cell in column:
                    try:
                        if len(str(cell.value)) > max_length:
                            max_length = len(str(cell.value))
                    except:
                        pass
                adjusted_width = min(max_length + 2, 30)
                ws.column_dimensions[column_letter].width = adjusted_width

            # 保存到内存
            output = io.BytesIO()
            wb.save(output)
            output.seek(0)

            # 生成文件名
            current_date = datetime.now().strftime('%Y%m%d')
            filename = f"销售列表_{current_date}.xlsx"

            return send_file(
                output,
                as_attachment=True,
                download_name=filename,
                mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )

        except Exception as e:
            print(f"导出Excel文件失败: {str(e)}")
            return jsonify({
                'success': False,
                'message': f'导出失败: {str(e)}'
            }), 500

    

    def res_to_dic(self,res,keys):
        dic_arr=[]
        for n,item in enumerate(res):
            dic={}
            for k,value in enumerate(item):
                dic[keys[k]]=value
            dic_arr.append(dic)
        return dic_arr
    
    def serve_jade_image(self):
        """提供玉石产品图片服务"""
        try:
            product_id = request.args.get('product_id')
            filename = request.args.get('filename')
            
            if not product_id:
                return jsonify({'res': 'failed', 'msg': '缺少产品ID参数'}), 400
            
            # 获取图片目录配置
            with open(os.path.join('./config','ejj_jade.config'),'r',encoding='utf-8') as f:
                config = json.load(f)
            
            jade_dir = config.get('jade_dir', '')
            if not jade_dir:
                return jsonify({'res': 'failed', 'msg': '未配置图片目录'}), 500
            
            # 构建图片路径
            if filename:
                # 如果指定了文件名，返回对应的图片
                image_path = os.path.join(jade_dir, product_id, filename)
            else:
                # 如果没有指定文件名，返回封面图片
                image_path = os.path.join(jade_dir, product_id, f'{product_id}-cover.jpg')
            
            # 如果图片不存在，使用默认图片
            if not os.path.exists(image_path):
                default_image = os.path.join(jade_dir, 'default_cover.jpg')
                if os.path.exists(default_image):
                    return send_file(default_image, mimetype='image/jpeg')
                else:
                    return jsonify({'res': 'failed', 'msg': '图片文件不存在'}), 404
            
            # 返回图片文件
            return send_file(image_path, mimetype='image/jpeg')
            
        except Exception as e:
            print(f'提供玉石图片失败: {str(e)}')
            return jsonify({'res': 'failed', 'msg': f'提供图片失败: {str(e)}'}), 500
    
    def deal_ej_sale(self):
        """处理销售信息，写入sales_table，并在成功后重新获取有效库存数据"""
        try:
            data = request.json
            print("接收到的销售数据:", data)
            
            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 构建插入SQL语句，包含所有sales_table字段
            sql = """
                INSERT INTO sales_table (
                    sale_datetime, buyer_id, buyer_name, buyer_company, stock_id, 
                    sale_price, sale_qty, sale_discount, sale_real_price, sale_total_price, 
                    saler_id, saler_name, settlement_status, settled_price, settled_datetime, 
                    settle_payer, comment, created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            # 准备插入值
            values = (
                data.get('sale_datetime'),
                data.get('buyer_id'),
                data.get('buyer_name'),
                data.get('buyer_company'),
                data.get('stock_id'),
                float(data.get('sale_price', 0)),
                float(data.get('sale_qty', 0)),
                float(data.get('sale_discount', 0)),
                float(data.get('sale_real_price', 0)),
                float(data.get('sale_total_price', 0)),
                data.get('saler_id'),
                data.get('saler_name'),
                'pending',  # 默认结算状态为pending
                float(data.get('settled_price', 0)) if data.get('settled_price') else None,
                data.get('settled_datetime') if data.get('settled_datetime') else None,
                data.get('settle_payer'),
                data.get('comment', ''),
                current_time,
                current_time
            )
            
            # 执行插入
            cursor.execute(sql, values)
            
            # 提交事务
            conn.commit()
            
            # 销售成功后，重新获取有效的库存数据
            valid_stock_sql = """
                SELECT 
                    s.stock_id,
                    s.product_id,
                    s.qty as stock_qty,
                    COALESCE(SUM(sa.sale_qty), 0) as total_sale_qty,
                    (s.qty - COALESCE(SUM(sa.sale_qty), 0)) as available_qty
                FROM stock_table s
                LEFT JOIN sales_table sa ON s.stock_id = sa.stock_id
                GROUP BY s.stock_id, s.product_id, s.qty
                HAVING (s.qty - COALESCE(SUM(sa.sale_qty), 0)) > 0
                ORDER BY s.stock_id
            """
            
            cursor.execute(valid_stock_sql)
            valid_stocks = cursor.fetchall()
            
            fields = [
                "stock_id",
                "product_id", 
                "stock_qty",
                "total_sale_qty",
                "available_qty"
            ]
            
            valid_stocks_dic = self.res_to_dic(valid_stocks, fields)
            
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': '销售信息保存成功',
                'sale_id': cursor.lastrowid,
                'valid_stocks': valid_stocks_dic,
                'valid_stocks_count': len(valid_stocks_dic)
            })
            
        except Exception as e:
            print(f"处理销售信息失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'保存失败: {str(e)}'
            }), 500

    def deal_stock_and_settle(self):
        """处理库存和结算信息，写入stock_table和settlement_stock_table"""
        try:
            data = request.json
            print("接收到的库存数据:", data)
            
            # 连接数据库
            conn = self.connect_mysql_ej()
            cursor = conn.cursor()
            
            # 检查stock_id是否已存在
            stock_id = data.get('stock_id')
            if stock_id:
                check_sql = "SELECT COUNT(*) FROM stock_table WHERE stock_id = %s"
                cursor.execute(check_sql, (stock_id,))
                stock_exists = cursor.fetchone()[0]
                
                if stock_exists > 0:
                    return jsonify({
                        'success': False,
                        'message': f'库存编号 {stock_id} 已存在，请使用唯一的库存编号'
                    }), 400
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 1. 写入stock_table
            stock_sql = """
                INSERT INTO stock_table (
                    stock_id, stock_name, product_id, product_name, type_1, type_2,
                    material_price, manual_price, transfer_price, other_price, qty, 
                    purchase_date, comment, created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            stock_values = (
                stock_id,
                data.get('stock_name'),
                data.get('product_id'),
                data.get('product_name'),
                data.get('type_1'),
                data.get('type_2'),
                float(data.get('material_price', 0)),
                float(data.get('manual_price', 0)),
                float(data.get('transfer_price', 0)),
                float(data.get('other_price', 0)),
                float(data.get('quantity', 0)),
                data.get('purchase_date', current_time),
                data.get('comment', ''),
                current_time,
                current_time
            )
            
            cursor.execute(stock_sql, stock_values)
            
            # 2. 写入settlement_stock_table - 为每种结算类型生成一条记录
            settlement_sql = """
                INSERT INTO settlement_stock_table (
                    stock_id, settlement_type, settlement_status, amount, 
                    settled_amount, balance_amount, advance_payer, settlement_payer, 
                    advance_date, settlement_date, comment, created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            # 计算各成本金额
            material_amount = float(data.get('material_price', 0)) * float(data.get('quantity', 0))
            manual_amount = float(data.get('manual_price', 0)) * float(data.get('quantity', 0))
            transfer_amount = float(data.get('transfer_price', 0)) * float(data.get('quantity', 0))
            other_amount = float(data.get('other_price', 0)) * float(data.get('quantity', 0))
            
            # 处理支付对象字段，确保不为空字符串
            material_payer = data.get('material_payer') or None
            manual_payer = data.get('manual_payer') or None
            transfer_payer = data.get('transfer_payer') or None
            other_payer = data.get('other_payer') or None
            
            # 为每种结算类型生成一条记录，共4条记录
            settlement_types = [
                ('material', material_amount, material_payer),
                ('manual', manual_amount, manual_payer),
                ('transfer', transfer_amount, transfer_payer),
                ('other', other_amount, other_payer)
            ]
            
            for settlement_type, amount, payer in settlement_types:
                # 设置结算状态：根据advance_payer判断
                if payer and amount > 0:  # 有支付对象且金额大于0
                    settlement_status = 'pending'  # 需要结算
                    advance_payer = payer  # 设置预付款支付人
                else:
                    settlement_status = 'not_necessary'  # 不需要结算
                    advance_payer = None  # 没有预付款支付人
                
                # settlement_payer在第一次录入时始终为空
                settlement_payer = None
                
                settlement_values = (
                    data.get('stock_id'),
                    settlement_type,
                    settlement_status,  # 根据支付对象和金额设置状态
                    amount,
                    0.0,  # settled_amount 初始为0
                    amount,  # balance_amount 初始等于amount
                    advance_payer,  # advance_payer（根据条件设置）
                    settlement_payer,  # settlement_payer（第一次录入为空）
                    data.get('purchase_date', current_time),  # advance_date
                    None,  # settlement_date 初始为null
                    data.get('comment', ''),
                    current_time,
                    current_time
                )
                cursor.execute(settlement_sql, settlement_values)
            
            # 提交事务
            conn.commit()
            cursor.close()
            conn.close()
            
            return jsonify({
                'success': True,
                'message': '库存和结算信息保存成功',
                'stock_id': data.get('stock_id')
            })
            
        except Exception as e:
            print(f"处理库存和结算信息失败: {str(e)}")
            if 'conn' in locals():
                conn.rollback()
                cursor.close()
                conn.close()
            return jsonify({
                'success': False,
                'message': f'保存失败: {str(e)}'
            }), 500

    ###学习星球####

    def md_to_pic2_page(self):
       return render_template('/md_to_pic2.html') 

    def how_to_exp_order(self):
       return render_template('/how_to_exp_order.html') 
    
    def md_to_pic(self):
        return render_template('/markdown_to_pic.html')
       
    def md_to_pic2(self):
        return render_template('/md_to_pic2.html')
       
    def new_ktt_deal_xlsx_from_page(self):
        file = request.files.get('file')
        seq_no = request.form.get('seq_no', '01')  # 默认为01
        if not file or not file.filename.endswith('.xlsx'):
            return jsonify({'res': 'failed', 'msg': '请上传有效的xlsx文件'})

        try:
            # 读取xlsx文件
            df = self._read_xlsx_from_page(file, password='7788', sheet_name='商品列表')
            
            # 从文件名提取8位日期作为folder_date
            if not re.match(r'^\d{8}', file.filename):
                raise Exception("文件名格式错误，必须以8位数字开头")
            folder_date = file.filename[:8]
            
            # 处理订单
            res = ktt_core_new.orders(
                config_dir=self.ktt_config['syn_config_root'],
                save_temp_root_dir=self.ktt_config['export_root_dir']
            ).exp_order(df=df, seq_no=seq_no, expand_qty='yes', return_stream=True, folder_date=folder_date)

            # 生成zip文件
            zip_buffer, zip_filename = self._generate_orders_zip(res)
            if not zip_buffer:
                return jsonify({'res': 'failed', 'msg': '生成订单文件失败'})

            # 保存zip文件
            temp_dir = os.path.join(self.ktt_config['export_root_dir'], 'temp')
            os.makedirs(temp_dir, exist_ok=True)
            zip_path = os.path.join(temp_dir, zip_filename)
            with open(zip_path, 'wb') as f:
                f.write(zip_buffer.getvalue())

            # 准备统计数据
            total_orders = len(df)
            success_orders = total_orders  # 假设全部成功
            products = {}
            combined_stats = []
            
            for product_name, product_data in res.items():
                if 'total_txt' in product_data:
                    combined_stats.append(f"{product_name}\n{product_data['total_txt']}")
                products[product_name] = len(product_data['df'])

            # 返回结果
            return jsonify({
                'res': 'ok',
                'data': {
                    'total_orders': total_orders,
                    'success_orders': success_orders,
                    'failed_orders': 0,
                    'products': [{'name':k, 'count':v} for k,v in products.items()],
                    'combined_stats_text': "\n\n".join(combined_stats),
                    'download_url': f'/download_orders?filename={zip_filename}'
                }
            })

        except Exception as e:
            return jsonify({'res': 'failed', 'msg': f'处理文件失败: {str(e)}'})
    
    def _read_xlsx_from_page(self, file_obj, password='7788', sheet_name='商品列表'):
        temp_path = None
        try:
            # 先尝试无密码读取(检查文件是否有密码保护)
            try:
                file_obj.seek(0)
                pd.read_excel(file_obj, sheet_name=sheet_name, engine='openpyxl')
                file_obj.seek(0)
                # 无密码保护，直接读取
                return pd.read_excel(file_obj, sheet_name=sheet_name, engine='openpyxl')
            except:
                # 文件可能有密码保护
                if platform.system().lower() == 'windows':
                    try:
                        pythoncom.CoInitialize()
                        # 创建临时文件处理
                        temp_path = os.path.join(tempfile.gettempdir(), f'temp_{uuid.uuid4().hex}.xlsx')
                        # 确保以二进制模式保存文件
                        with open(temp_path, 'wb') as f:
                            file_obj.seek(0)
                            f.write(file_obj.read())
                        
                        # 验证文件完整性
                        if not os.path.exists(temp_path) or os.path.getsize(temp_path) == 0:
                            raise Exception("临时文件保存失败")
                        
                        xlsApp = win32com.client.DispatchEx('Excel.Application')
                        xlsApp.EnableEvents = False
                        xlsApp.DisplayAlerts = False
                        
                        try:
                            xlwb = xlsApp.Workbooks.Open(
                                temp_path,
                                UpdateLinks=False,
                                ReadOnly=False,
                                Password=password
                            )
                        except Exception as e:
                            # 重试一次
                            try:
                                xlwb = xlsApp.Workbooks.Open(
                                    temp_path,
                                    UpdateLinks=False,
                                    ReadOnly=False,
                                    Password=password
                                )
                            except Exception as e2:
                                raise Exception(f"无法打开Excel文件(重试后失败): {str(e2)}")
                        
                        # 读取数据到DataFrame
                        df = pd.DataFrame(list(xlwb.Sheets(sheet_name).UsedRange.Value))
                        df.columns = df.iloc[0]
                        df = df[1:]
                        
                        xlwb.Close(False)
                        xlsApp.Quit()
                        return df
                    except Exception as e:
                        raise Exception(f"无法打开密码保护的Excel文件: {str(e)}")
                    finally:
                        pythoncom.CoUninitialize()
                        if temp_path and os.path.exists(temp_path):
                            try:
                                os.remove(temp_path)
                            except Exception as e:
                                print(f"Error removing temp file {temp_path}: {str(e)}")
                else:
                    raise Exception("非Windows系统无法处理密码保护的Excel文件")
        except Exception as e:
            raise Exception(f"处理Excel文件失败: {str(e)}")
    
    def new_ktt_page(self):
        return render_template('/new_ktt_deal.html')
    
    def get_new_ktt_page_config(self):
        res=ktt_core_new.orders(config_dir=self.ktt_config['syn_config_root'],save_temp_root_dir=self.ktt_config['export_root_dir']). exp_html_config()
        # print('html goods config:',res)

        return jsonify({'res':'ok','data':res})
    
    def _generate_orders_zip(self, data):
        """生成订单zip文件"""
        if not data or not isinstance(data, dict):
            return None, '无效的订单数据'
        
        try:
            # 检查所有文件流是否有效
            for product_name, product_data in data.items():
                if not hasattr(product_data.get('file_stream'), 'getvalue'):
                    return None, f'{product_name}的文件流无效'
                if not product_data.get('output_path'):
                    return None, f'{product_name}的输出路径缺失'

            # 生成zip文件
            # 从第一个文件名中提取前8位作为日期
            first_product = next(iter(data.values()))
            # print('out put path:',first_product['output_path'])
            filename = os.path.basename(first_product['output_path'])  # 获取纯文件名
            date_str = filename.split('-')[0][:8]  # 提取前8位日期
            # date_str = first_product['output_path'].split('\\')[-1].split('-')[0][:8]
            datetime_str = datetime.now().strftime("%Y%m%d%H%M%S")
            zip_filename = f"{date_str}-export-orders.zip"
            # 彻底清理文件名，去除所有可能的多余下划线
            zip_filename = zip_filename.strip('_').replace('__', '_')
            zip_buffer = io.BytesIO()
            
            with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file:
                for product_name, product_data in data.items():
                    file_name = product_data['output_path'].split('\\')[-1]
                    file_content = product_data['file_stream'].getvalue()
                    if not file_content:
                        return None, f'{product_name}的文件内容为空'
                    # 在zip中使用文件名的前8位作为文件夹
                    zip_path = f"{date_str}/{file_name}"
                    zip_file.writestr(zip_path, file_content)
            
            zip_buffer.seek(0)
            return zip_buffer, zip_filename

        except Exception as e:
            return None, f'生成压缩文件失败: {str(e)}'

    def deal_submit_ktt_orders(self):
        data = request.json
        print('received data:', data)
        
        # 初始化统计数据
        stats = {
            'total_orders': 0,
            'success_orders': 0,
            'failed_orders': 0,
            'products': {},
            'combined_stats_text':'',
            'zip_filename': ''
        }
        
        # 处理订单数据
        all_orders = []
        error_msgs = []
        for order in data['orders']:
            # print('order in data.orders:',order)
            stats['total_orders'] += 1
            try:
                # 预处理：过滤空行
                lines = [line.strip() for line in order['addresses'].split('\n') if line.strip()]
                
                # 检查行数是否为偶数
                if len(lines) % 2 != 0:
                    error_msgs.append("订单信息不完整，最后一条订单缺少收货信息")
                    stats['failed_orders'] += (len(lines) + 1) // 2  # 预估失败订单数
                    continue
                
                # 每2行一组处理
                for i in range(0, len(lines), 2):
                    print('i:',i)
                    try:
                        
                        # 校验订单编号行
                        if not lines[i].startswith('订单编号'):
                            raise ValueError(f"第{i+1}行：无效的订单编号格式")
                        order_no = lines[i][5:].strip()  # 去掉"订单编号："前缀                       
                        
                            
                        # 校验收货信息行
                        if not lines[i+1].startswith('收货信息'):
                            raise ValueError(f"第{i+2}行：缺少收货信息前缀")

                             
                        # 解析收货信息
                        addr_line = lines[i+1][5:].strip()  # 去掉"收货信息："前缀
                        normalized_line = addr_line.replace('，', ',')
                        parts = normalized_line.rsplit(',', 1)
                        if len(parts) != 2:
                            raise ValueError("缺少数量分隔符逗号")
                            
                        address_info, quantity = parts
                        address_parts = address_info.split(' ')
                        if len(address_parts) < 3:
                            raise ValueError("收货信息格式不正确")
                            
                        # 构建订单记录
                        all_orders.append({
                            '订单号': order_no,
                            '分类': order['category'],
                            '商品': order['spec'], 
                            '商品编码': order['product_id'],
                            '数量': quantity,
                            '收货人': address_parts[0],
                            '联系电话': address_parts[1],
                            '详细地址': ' '.join(address_parts[2:])
                        })
                        stats['success_orders'] += 1

                        
                        
                        # 统计商品
                        if order['category'] not in stats['products']:
                            stats['products'][order['category']] = 0
                        stats['products'][order['category']] += 1
                    except Exception as e:
                        error_msgs.append(str(e))
                        stats['failed_orders'] += 1
            except Exception as e:
                error_msgs.append(str(e))
                stats['failed_orders'] += (len(lines) + 1) // 2  # 预估失败订单数
                print(f"处理订单失败: {str(e)}")
        
        # 转换为DataFrame
        print('all_orders:',all_orders)
        df = pd.DataFrame(all_orders)
        print('Processed orders:\n', df)

        res=ktt_core_new.orders(config_dir=self.ktt_config['syn_config_root'],save_temp_root_dir=self.ktt_config['export_root_dir']).exp_order(df=df, seq_no=data['seq_no'], expand_qty='yes', return_stream=True)

        # print('exp order res:',res)
        # 生成zip文件
        zip_buffer, error_msg = self._generate_orders_zip(res)
        if not zip_buffer:
            return jsonify({'res': 'failed', 'msg': error_msg})
        
        stats['zip_filename'] = error_msg.strip('_').replace('__', '_')
        
        # 保存zip文件到临时目录
        temp_dir = os.path.join(self.ktt_config['export_root_dir'], 'temp')
        os.makedirs(temp_dir, exist_ok=True)
        zip_path = os.path.join(temp_dir, stats['zip_filename'])
        with open(zip_path, 'wb') as f:
            f.write(zip_buffer.getvalue())
        
        # 统一返回JSON
            # 拼接所有模板的统计文本
            combined_stats = []
            for template_name, template_data in res.items():
                if 'total_txt' in template_data:
                    combined_stats.append(f"{template_name}\n{template_data['total_txt']}")
            
            return jsonify({
                'res': 'ok',
                'data': {
                    'total_orders': stats['total_orders'],
                    'success_orders': stats['success_orders'],
                    'failed_orders': stats['failed_orders'],
                    'products': [{'name':k, 'count':v} for k,v in stats['products'].items()],
                    'combined_stats_text': "\n\n".join(combined_stats),
                    'download_url': f'/download_orders?filename={stats["zip_filename"]}'
                }
            })


    def connect_mysql(self):
        with open(os.path.join(os.path.dirname(os.path.realpath((__file__))),'config','db.config'),'r',encoding='utf-8') as f:
            cfg=json.load(f)
        # 连接数据库
        conn = pymysql.connect(
            host=cfg['host'],       # 数据库主机地址
            user=cfg['user'],     # 数据库用户名
            password=cfg['password'], # 数据库密码
            database=cfg['database'],  # 要连接的数据库名称
            port=cfg['port']
        )

        return conn
    
    def connect_mysql_ej(self):
        with open(os.path.join(os.path.dirname(os.path.realpath((__file__))),'config','db_ejj.config'),'r',encoding='utf-8') as f:
            cfg=json.load(f)
        # 连接数据库
        conn = pymysql.connect(
            host=cfg['host'],       # 数据库主机地址
            user=cfg['user'],     # 数据库用户名
            password=cfg['password'], # 数据库密码
            database=cfg['database'],  # 要连接的数据库名称
            port=cfg['port']
        )

        return conn

    
    def zwds_page(self):        
        return render_template('/zwds.html')
    
    def check_suibi_files(self):
        data=request.json
        start_date=data['start_date']
        date_str=''.join(start_date.split('-'))
        output_dir=self.config_ej['output_dir'] 

        res_dates=[] 
        valid_dates=[]
        temp_mp3_files={}
        try:
            print('check suibi files...')            
   
            dir_name=f'{date_str[:4]}-{date_str[4:6]}-{date_str[6:]}'      
            check_file_names =[f'{date_str}-suiBi_video.mp4']
                    
            pic_dir=os.path.join(output_dir,'日穿搭',dir_name)

            
            
            if os.path.isdir(pic_dir):                
                valid_dates.append(date_str)
                temp_mp3_files[date_str]=''
                for fn in os.listdir(pic_dir):
                    # print(fn,check_file_names,fn in check_file_names,res_dates,len(res_dates))
                    if fn in check_file_names:                        
                        res_dates.append(date_str)
                    if fn.split('.')[-1].lower()=='mp3':
                        temp_mp3_files[date_str]='processing'

            if len(res_dates)>0 and temp_mp3_files[date_str]!='processing':                
                return jsonify({'res':'ok','data':res_dates})
            else:
                return jsonify({'res':'failed','error':'no data'})
        except Exception as e:
            if len(res_dates)>0 and temp_mp3_files[date_str]!='processing':    
                return jsonify({'res':'ok','data':res_dates})
            else:
                return jsonify({'res':'failed','error':f'Exception occurred: {str(e)}'})
    
    def check_riyun_files(self):
        data=request.json
        file_types=data['file_types']
        start_date=data['start_date']
        end_date=data['end_date']

        output_dir=self.config_ej['output_dir']

        datelist=[]
        stime,etime=datetime.strptime(start_date,'%Y-%m-%d'),datetime.strptime(end_date,'%Y-%m-%d')
        while stime<=etime:
            datelist.append(stime.strftime('%Y%m%d'))
            stime+=timedelta(days=1)

        res_dates=[]          
        miss_files={}  
        valid_dates=[]
        temp_mp3_files={}
        try:
            print('check riyun files...')
            for date_str in datelist:     
                miss_files[date_str]=[] 
                dir_name=f'{date_str[:4]}-{date_str[4:6]}-{date_str[6:]}'      
                if file_types=='img_txt':
                    check_file_names = ['1-木.jpg', '2-火.jpg', '3-土.jpg', '4-金.jpg', '5-水.jpg', f'{dir_name}_日运.txt',  f'{dir_name}-抖音封面.jpg',  f'{dir_name}-抖音内容.jpg']
                else:
                    check_file_names = ['1-木.jpg', '2-火.jpg', '3-土.jpg', '4-金.jpg', '5-水.jpg', f'{dir_name}_日运.txt',  f'{dir_name}-抖音封面.jpg',  f'{dir_name}-抖音内容.jpg',f'{date_str}-output_video.mp4']
                        
                pic_dir=os.path.join(output_dir,'日穿搭',dir_name)

                

                if os.path.isdir(pic_dir):
                    valid_dates.append(date_str)
                    directory_files = set(os.listdir(pic_dir))
                    miss_files[date_str]=[fn for fn in check_file_names if fn not in directory_files]
                    temp_mp3_files[date_str]=''

                    for fn in os.listdir(pic_dir):
                        if file_types=='img_txt_video' and fn.split('.')[-1].lower()=='mp3':
                            temp_mp3_files[date_str]='processing'


            for date_str in valid_dates:
                print(miss_files[date_str],temp_mp3_files[date_str])
                if len(miss_files[date_str])<=0 and temp_mp3_files[date_str]!='processing':
                    if date_str not in res_dates:
                            res_dates.append(date_str)


            print('miss files',miss_files)
            if res_dates:                
                return jsonify({'res':'ok','data':res_dates})
            else:
                return jsonify({'res':'failed','error':'no data'})
        except Exception as e:
            if res_dates:
                return jsonify({'res':'ok','data':res_dates})
            else:
                return jsonify({'res':'failed','error':f'Exception occurred: {str(e)}'})


    #获取坚果云学习星球的背景图片
    def get_ddstudy_bg_list(self):
        with open('../web/config/dogduck.config','r',encoding='utf-8') as f:
            config=json.load(f)
        bg_dir=config['bg_pic_dir']
    
        bg_list={}
        for root,dirs,fn in os.walk(bg_dir):
            for dir in dirs:
                bg_list[dir]=[]
                for fn in os.listdir(os.path.join(bg_dir,dir)):
                    if fn not in bg_list[dir] and fn.split('.')[-1].lower() in ['jpg','png','jpeg']:
                        bg_list[dir].append(os.path.join(bg_dir,dir,fn))
        # print(bg_list)

        return jsonify({'res':'ok','data':bg_list})

   

    def serve_bg_image(self):
        """提供背景图片服务"""
        try:
            image_path = request.args.get('path')
            if not image_path:
                return jsonify({'res': 'failed', 'msg': '缺少图片路径参数'}), 400

            # 解码URL编码的路径
            if platform.system().lower()=='windows':
                image_path = image_path.replace('/', '\\')  # 确保使用正确的路径分隔符


            # 验证文件是否存在且是图片文件
            if not os.path.exists(image_path):
                return jsonify({'res': 'failed', 'msg': '图片文件不存在'}), 404

            # 检查文件类型
            if not image_path.lower().endswith(('.jpg', '.jpeg', '.png')):
                return jsonify({'res': 'failed', 'msg': '不支持的文件类型'}), 400

            # 返回图片文件
            return send_file(image_path, mimetype='image/jpeg')

        except Exception as e:
            print(f"提供背景图片失败: {str(e)}")
            return jsonify({'res': 'failed', 'msg': f'提供图片失败: {str(e)}'}), 500


    #学习星球小红书图片
    def generate_xhs_image(self):
        from io import BytesIO
        try:
            # 获取前端提交的数据
            # print("接收到的请求内容类型:", request.content_type)
            if request.content_type.startswith('multipart/form-data'):
                # 处理FormData请求
                text = request.form.get('text')
                font_size = int(request.form.get('font_size'))
                font = request.form.get('font')
                ratio = request.form.get('ratio')
                bg_type = request.form.get('bgType')
                # print(f"接收到的参数 - text: {text}, font_size: {font_size}, font: {font}, ratio: {ratio}, bg_type: {bg_type}")
                
                if bg_type == 'image':
                    bg_source = request.form.get('bg_source', 'custom')
                    if bg_source == 'default':
                        # 加载默认背景图片
                        ratio = request.form.get('ratio', '3:4')
                        bg_fn = f'txt_bg_{ratio.replace(":", "_")}.jpg'
                        bg_path = os.path.join(self.config_ej['work_dir'], '素材', '学习星球', '背景图', bg_fn)
                        # print(f"使用默认背景图片: {bg_path}")
                        bg_img = Image.open(bg_path)
                    else:
                        # print("处理自定义图片背景...")
                        bg_img = request.files['bg_img']
                        # print(f"接收到的图片文件: {bg_img.filename}")
                        bg_img = Image.open(bg_img.stream)
                        # print("图片已成功打开")
                else:
                    bg_color = request.form.get('bg_color', '#293931')
                    # print(f"使用颜色背景: {bg_color}")
                    bg_img = None
            else:
                # 处理JSON请求（兼容旧版本）
                data = request.json
                text = data['text']
                font_size = int(data['font_size'])
                font = data['font']
                ratio = data['ratio']
                bg_color = data.get('bg_color', '#293931')
                bg_img = None
            
            # 调用图片生成逻辑
            p = dd_ppt.xhsImage()
            if bg_img:
                img = p.dd_image(txt=text, region=(100,130,900,700), 
                                initial_font=font, font_size=font_size, 
                                color='white', intended='no', 
                                ratio=ratio, bg_img=bg_img)
            else:
                img = p.dd_image(txt=text, region=(100,130,900,700), 
                                initial_font=font, font_size=font_size, 
                                color='white', intended='no', 
                                ratio=ratio, bg_color=bg_color)
            
            # 将图片保存到内存字节流
            img_bytes = BytesIO()
            img.save(img_bytes, format='PNG', quality=90)
            img_bytes.seek(0)
            
            # 创建响应对象
            response = make_response(img_bytes.getvalue())
            response.headers.set('Content-Type', 'image/png')
            response.headers.set('Content-Disposition', 'inline', 
                                filename=f"{datetime.now().strftime('%Y%m%d%H%M%S')}.png")
            
            return response
    
        except Exception as e:
            # 错误处理
            return jsonify({
                'error': True,
                'message': str(e)
            }), 500

    def generate_xhs_cover_image(self):
        from io import BytesIO
        try:
            # 获取前端提交的数据
            cover_image = request.files['cover_image']
            main_title = request.form['main_title']
            main_title_font = request.form['main_title_font']
            main_title_size = int(request.form['main_title_size'])
            main_title_color = request.form['main_title_color']
            sub_title = request.form['sub_title'] 
            sub_title_font = request.form['sub_title_font']
            sub_title_size = int(request.form['sub_title_size'])
            sub_title_color = request.form['sub_title_color']
            opacity = int(request.form.get('opacity', 100))  # 默认100%不透明
            aspect_ratio = request.form.get('aspect_ratio', '16:9')  # 获取分辨率参数
            
            # 调用封面生成逻辑
            p = dd_ppt.xhsCover()
            bg=Image.open(cover_image)
            img = p.generate_cover(                
                cover_img=bg,
                main_title=main_title,
                main_title_font=main_title_font,
                main_title_size=main_title_size,
                main_title_color=main_title_color,
                sub_title=sub_title,
                sub_title_font=sub_title_font,
                sub_title_size=sub_title_size,
                sub_title_color=sub_title_color,
                opacity=opacity,
                aspect_ratio=aspect_ratio  # 传递分辨率参数
            )
            
            # 将图片保存到内存字节流
            img_bytes = BytesIO()
            img.save(img_bytes, format='PNG', quality=90)
            img_bytes.seek(0)
            
            # 创建响应对象
            response = make_response(img_bytes.getvalue())
            response.headers.set('Content-Type', 'image/png')
            response.headers.set('Content-Disposition', 'inline', filename='xhs_cover.png')
            
            return response
            
        except Exception as e:
            # 错误处理
            return jsonify({
                'error': True,
                'message': str(e)
            }), 500

    def md_pic_to_video(self):
        print('generate video...')
        temp_dir = None
        img_paths = []
        output_path = None
        try:
            data = request.json
            images = data.get('images', [])
            # print('images:',images)
            resolution = data.get('resolution', '960x1280')
            
            if not images:
                return jsonify({'res': 'failed', 'msg': '没有接收到图片数据'})
            
            # 从配置文件读取临时目录
            cfg = os.path.join(os.path.dirname(__file__), 'config', 'dogduck.config')
            print(f"配置文件路径: {cfg}")
            print(f"文件是否存在: {os.path.exists(cfg)}")
            
            try:
                with open(cfg, 'r',encoding='utf-8') as f:
                    print("成功打开配置文件")
                    config = json.load(f)
                    print('config内容:', config)
                    temp_dir = config.get('temp_video_dir', 'temp_video')
                    print('使用的临时目录:', temp_dir)
                    os.makedirs(temp_dir, exist_ok=True)
            except json.JSONDecodeError as e:
                print(f"JSON解析错误: {str(e)}")
                raise
            except Exception as e:
                print(f"配置文件读取错误: {str(e)}")
                raise
            
            # 下载并验证图片到临时目录
            for i, img_data in enumerate(images):
                try:
                    # 检查数据是否包含base64前缀
                    if not img_data.startswith('data:image/'):
                        raise ValueError('无效的图片格式')
                        
                    # 分离base64数据部分
                    header, encoded = img_data.split(',', 1)
                    img_format = header.split('/')[1].split(';')[0]
                    
                    # 解码并验证图片
                    decoded = base64.b64decode(encoded)
                    img = Image.open(io.BytesIO(decoded))
                    img.verify()  # 验证图片完整性
                    
                    # 保存图片
                    img_path = os.path.join(temp_dir, f'img_{i}.{img_format}')
                    with open(img_path, 'wb') as f:
                        f.write(decoded)
                    img_paths.append(img_path)
                    
                except Exception as e:
                    print(f"图片{i}无效: {str(e)}")
                    continue
            
            # 使用FFmpeg创建视频
            output_path = os.path.join(temp_dir, 'output.mp4')
            ffmpeg=config.get('ffmpeg','ffmpeg')
            ffprobe=config.get('ffprobe','ffprobe')

            # 获取每张图片显示时间
            time_per_pic=str(data.get('time_per_pic',4))

            print(ffmpeg,ffprobe)
            
            # 处理音乐文件
            bg_audio = None
            duration = None
            
            # 1. 优先处理上传音乐
            if data.get('music_file_data'):
                try:
                    bg_audio = self._process_uploaded_music(data, temp_dir, ffmpeg)
                except Exception as e:
                    print(f"处理上传音乐失败: {str(e)}")
                    raise
            
            # 2. 如果没有上传音乐，处理预设音乐
            if bg_audio is None and data.get('music_style'):
                try:
                    bg_audio = self._process_preset_music(data, config, temp_dir)
                    if bg_audio is None:
                        print("警告: 预设音乐目录中没有可用的MP3文件")
                except Exception as e:
                    print(f"处理预设音乐失败: {str(e)}")
                    raise
            
            # 生成无音频视频(无论是否有音乐都先生成无音频版本)
            video_cmd = [
                ffmpeg,
                '-framerate', f'1/{time_per_pic}',
                '-i', f'{temp_dir}/img_%d.png',
                '-c:v', 'libx264',
                '-r', '30',
                '-pix_fmt', 'yuv420p',
                '-movflags', '+faststart',
                '-profile:v', 'main',
                '-preset', 'fast',
                '-crf', '23',
                '-an',  # 无音频
                '-y',  # 覆盖输出文件
                output_path
            ]
            subprocess.run(video_cmd, check=True)
            
            # 获取视频时长
            probe_cmd = [ffprobe, '-v', 'error', '-show_entries', 'format=duration', 
                       '-of', 'default=noprint_wrappers=1:nokey=1', output_path]
            duration = float(subprocess.check_output(probe_cmd).decode().strip())
            
            # 如果有音乐，合并音频
            if bg_audio:
                try:
                    bg_audio, fade_params = self._process_music(bg_audio, duration, temp_dir, ffmpeg, ffprobe)
                    
                    # 合并音频(带淡出效果)
                    final_output = os.path.join(temp_dir, 'final_output.mp4')
                    audio_cmd = [
                        ffmpeg,
                        '-i', output_path,
                        '-i', bg_audio,
                        '-filter_complex', 
                        f'[1:a]afade={fade_params}[a1];[a1]volume=0.5[a2]',
                        '-map', '0:v',
                        '-map', '[a2]',
                        '-c:v', 'copy',
                        '-c:a', 'aac',  # 明确指定音频编码
                        '-b:a', '192k',  # 设置音频比特率
                        '-shortest',
                        '-y',  # 覆盖输出文件
                        final_output
                    ]
                    print("执行音频合并命令:", " ".join(audio_cmd))
                    result = subprocess.run(audio_cmd, check=True, capture_output=True, text=True, encoding='utf-8', errors='ignore')
                    print("命令输出:", result.stdout)
                    print("命令错误:", result.stderr)
                    os.replace(final_output, output_path)
                    print("视频文件替换完成")
                    
                    # 如果是上传音乐，需要额外清理临时文件
                    if 'uploaded_music' in bg_audio:
                        os.remove(bg_audio)
                        print(f"已删除上传的音乐文件: {bg_audio}")
                except Exception as e:
                    print(f"合并音频失败: {str(e)}")
                    raise
            
            # 返回视频文件
            if not os.path.exists(output_path):
                raise FileNotFoundError('视频文件生成失败')
            
            # 验证视频文件
            print('验证视频文件')
            try:
                subprocess.run([ffprobe, output_path], check=True, capture_output=True)
            except subprocess.CalledProcessError:
                raise ValueError('生成的视频文件格式无效')
            
            # 确保文件可读并验证大小
            file_size = os.path.getsize(output_path)
            print(f"原始文件大小: {file_size} 字节")
            
            with open(output_path, 'rb') as f:
                video_data = f.read()
                print(f"实际读取大小: {len(video_data)} 字节")
                if len(video_data) != file_size:
                    raise ValueError(f"文件读取不完整: {len(video_data)}/{file_size} 字节")
            
            # 验证文件可读并验证大小
            file_size = os.path.getsize(output_path)
            print(f"原始文件大小: {file_size} 字节")
            
            # 使用chunked传输确保大文件传输
            def generate():
                with open(output_path, 'rb') as f:
                    while True:
                        chunk = f.read(8192)
                        if not chunk:
                            break
                        yield chunk
            
            # 创建响应对象
            response = Response(generate(), mimetype='video/mp4')
            response.headers['Content-Disposition'] = 'attachment; filename=markdown_video.mp4'
            response.headers['Content-Length'] = str(file_size)
            print(f"响应头设置完成，Content-Length: {file_size}")
            
            # 记录临时文件路径用于调试
            print(f"视频文件路径: {output_path}")
            
            # # 异步清理临时文件
            # import threading
            # t = threading.Timer(60.0, self._cleanup_temp_files, [temp_dir, img_paths, output_path])
            # t.start()
            
            return response
            
        except Exception as e:
            return jsonify({'res': 'failed', 'msg': f'视频生成失败: {str(e)}'})
        finally:
            # 确保无论如何都尝试清理
            self._cleanup_temp_files(temp_dir, img_paths, output_path)

    def _process_uploaded_music(self, data, temp_dir, ffmpeg):
        """处理上传的音乐文件"""
        try:
            music_data = data['music_file_data']
            file_ext = music_data['name'].split('.')[-1].lower()
            temp_path = os.path.join(temp_dir, f'uploaded_music.{file_ext}')
            
            # 保存并处理上传音乐
            with open(temp_path, 'wb') as f:
                f.write(base64.b64decode(music_data['content']))
            
            # 如果是MP4则提取音频
            if file_ext == 'mp4':
                audio_path = os.path.join(temp_dir, 'uploaded_music.mp3')
                cmd = [ffmpeg, '-i', temp_path, '-vn', '-acodec', 'libmp3lame', '-y', audio_path]
                subprocess.run(cmd, check=True)
                os.remove(temp_path)
                return audio_path
            return temp_path
        except Exception as e:
            print(f"处理上传音乐失败: {str(e)}")
            if 'temp_path' in locals() and os.path.exists(temp_path):
                os.remove(temp_path)
            if 'audio_path' in locals() and os.path.exists(audio_path):
                os.remove(audio_path)
            raise

    def _process_preset_music(self, data, config, temp_dir):
        """处理预设音乐"""
        try:
            music_style = data.get('music_style','modern')
            bg_audio_dir = config.get('bg_audio_chinese_dir' if music_style == 'traditional' else 'bg_audio_dir')
            
            if bg_audio_dir and os.path.exists(bg_audio_dir):
                mp3_files = [f for f in os.listdir(bg_audio_dir) if f.endswith('.mp3')]
                if mp3_files:
                    return os.path.join(bg_audio_dir, random.choice(mp3_files))
            return None
        except Exception as e:
            print(f"处理预设音乐失败: {str(e)}")
            raise

    def _process_music(self, bg_audio, video_duration, temp_dir, ffmpeg, ffprobe):
        """处理背景音乐(上传或预设)"""
        try:
            # 获取音频时长
            audio_duration_cmd = [ffprobe, '-v', 'error', '-show_entries', 
                                'format=duration', '-of', 
                                'default=noprint_wrappers=1:nokey=1', bg_audio]
            audio_duration = float(subprocess.check_output(audio_duration_cmd).decode().strip())
            
            # 如果音频长度不足，循环拼接
            if audio_duration < video_duration:
                loop_times = int(video_duration / audio_duration) + 1
                looped_audio = os.path.join(temp_dir, 'looped_audio.mp3')
                loop_cmd = [
                    ffmpeg,
                    '-stream_loop', str(loop_times),
                    '-i', bg_audio,
                    '-c', 'copy',
                    '-y', looped_audio
                ]
                subprocess.run(loop_cmd, check=True)
                bg_audio = looped_audio
            
            # 返回处理后的音频路径和淡出参数
            return bg_audio, f't=out:st={video_duration-1.8}:d=1.8'
        except Exception as e:
            print(f"处理音乐文件时出错: {str(e)}")
            raise

    def _cleanup_temp_files(self, temp_dir, img_paths, output_path):
        """统一的临时文件清理方法"""
        try:
            print(f"开始清理临时目录: {temp_dir}")
            # 删除图片文件
            for img_path in img_paths:
                if img_path and os.path.exists(img_path):
                    os.remove(img_path)
                    print(f"已删除图片: {img_path}")
            # 删除上传的音乐文件和循环音频
            music_path = os.path.join(temp_dir, 'uploaded_music.mp3')
            looped_audio = os.path.join(temp_dir, 'looped_audio.mp3')
            if os.path.exists(music_path):
                os.remove(music_path)
                print(f"已删除音乐文件: {music_path}")
            if os.path.exists(looped_audio):
                os.remove(looped_audio)
                print(f"已删除循环音频: {looped_audio}")
            # 删除视频文件
            # if output_path and os.path.exists(output_path):
            #     os.remove(output_path)
            #     print(f"已删除视频: {output_path}")
            print("临时文件清理完成")
        except Exception as e:
            print(f"清理临时文件时出错: {str(e)}")
            # 记录更详细的错误信息
            import traceback
            traceback.print_exc()
    
    def check_suibi_pic(self,date_input=None):
        if date_input is None:
            data=request.json
            date_input=data['dateInput']

        format_date=f'{date_input[:4]}{date_input[4:6]}{date_input[6:]}'
        work_dir=self.config_ej['work_dir']
        output_dir=self.config_ej['output_dir']
        pic_dir=os.path.join(output_dir,'日穿搭',date_input)
        pics=[]
        try:
            if os.path.exists(pic_dir):
                for fn in os.listdir(pic_dir):
                    if re.match(r'\d{8}-s\d{2}.jpg',fn):
                        pics.append(fn)
                        max_num=int(fn.split('.')[0].split('-')[1][1:])
            else:
                return jsonify({'res':'failed','error':'无随笔图片文件夹'})
            
            if len(pics)==0:
                return jsonify({'res':'failed','error':'无随笔图片'})
        except Exception as e:
            return jsonify({'res':'failed','error':'获取随笔图片错误'+str(e)})
            
        return jsonify({'res':'ok','pic_list':pics,'max_num':max_num})


    def mix_export_orders(self,date_input='20240725',
                        batch_num='01',
                      ktt_config='D:\\工作目录\\ktt\\order_col_map.config',
                      xlsx='E:\\temp\\ejj\\团购群\\订单\\20240724-综合-导出订单-01.xlsx',
                      sheet_name='商品列表',                      
                      spec_xlsx='D:\\工作目录\\ktt\\三恒商品规格编码.xlsx',
                      save_dir='E:\\temp\\ejj\\团购群\\订单\\给果园的订单',):
        if platform.system().lower()=='windows':
            pythoncom.CoInitialize()
        p=ktt_core.orders(ktt_config=ktt_config)
        save_names=p.batch_exp_save(date_input=date_input,batch_num=batch_num,xlsx=xlsx,sheet_name=sheet_name,spec_xlsx=spec_xlsx,save_dir=save_dir,open_dir='no')
        return save_names

    def deal_ktt_export(self):
        try:
            data=request.json
            print(data)
            fn_get=data['fileName'].split('-')[0]
            batch_num=data['fileName'].split('.')[0].split('-')[3]
            fn=os.path.join(self.ktt_config['ktt_temp_dir'],'ktt_export_xlsx',f'{fn_get[:4]}-{fn_get[4:6]}-{fn_get[6:]}',data['fileName'])
            save_dir=os.path.join(self.ktt_config['ktt_temp_dir'],'to_sender_xlsx')
     
            save_names=self.mix_export_orders(date_input=fn_get,
                            batch_num=batch_num,
                        ktt_config=os.path.join(self.ktt_config['syn_config_root'],'order_col_map.config'),
                        xlsx=fn,
                        sheet_name='商品列表',                      
                        spec_xlsx=os.path.join(self.ktt_config['syn_config_root'],'三恒商品规格编码.xlsx'),
                        save_dir=save_dir)
            # print(save_names['sum_txt'])
            # self.zip_and_download_mix_ktt(fn_get,batch_num)
            return jsonify({'res':'ok','data':save_names})
        except Exception as e:
            return jsonify({'res':'failed','error':str(e)})


    def allowed_file(self,filename):
        return "." in filename and filename.rsplit(".", 1)[1].lower() in {"xlsx"}

    def upload_file(self):
            fn_date=datetime.now().strftime('%Y-%m-%d')
            file = request.files["file"]
            filename =  request.args.get("filename")
   
            if not file.content_type in ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") or not file.filename.endswith((".xlsx")):
                return {"message": "没有选择文件 image"}, 400
            
            # file = request.files["image"]
            if file.filename == "":
                return {"message": "没有选择文件 empty"}, 400

            # if file.content_type.split('/')[0] != 'image':
            # # 文件不是图片类型，返回错误信息
            #     return {"message": "请上传图片文件"},400
            
            if file and self.allowed_file(filename):
                # filename = secure_filename(filename)
                # print('checked filename:',filename)
                dir_name=filename.split('-')[0]
                temp_dir=os.path.join(self.ktt_config['ktt_temp_dir'],'ktt_export_xlsx',f'{dir_name[:4]}-{dir_name[4:6]}-{dir_name[6:]}')
                if not os.path.exists(temp_dir):
                    os.makedirs(temp_dir)
                save_fn=os.path.join(temp_dir, filename)       
                file.save(save_fn)
                
                return {"message": "上传成功"}
            else:
                return {"message": "只能上传xlsx格式文件"}, 400
    
    def allowed_file_suibi(self,filename):
        return "." in filename and filename.rsplit(".", 1)[1].lower() in {"jpg","jpeg","png"}
    
    def upload_pic_suibi(self):
        fn_date=datetime.now().strftime('%Y-%m-%d')
        
        file = request.files["file"]
        filename =  request.args.get("filename")
        id_input=request.args.get('id')
        date_input=request.args.get('date_input')

        pics_data=self.check_suibi_pic(date_input=date_input)
        pics=pics_data.get_json()

        print('pics:',pics)

        if not "image/" in  file.content_type or not filename.endswith((".jpg", ".jpeg", ".png")):
            return {"message": "没有选择文件 image"}, 400
        
        # file = request.files["image"]
        if file.filename == "":
            return {"message": "没有选择文件 empty"}, 400

        # if file.content_type.split('/')[0] != 'image':
        # # 文件不是图片类型，返回错误信息
        #     return {"message": "请上传图片文件"},400
        
        if file and self.allowed_file_suibi(filename):
            # filename = secure_filename(filename)
            if pics['res']=='ok':
                pic_num=str(int(pics['max_num'])+1).zfill(2)
            else:
                pic_num='01'
            new_fn=f"{''.join(date_input.split('-'))}-s{pic_num}.jpg"
            print('checked filename:',filename)
            output_dir=self.config_ej['output_dir']    
            pic_dir=os.path.join(output_dir,'日穿搭',date_input)
            if not os.path.exists(pic_dir):
                os.makedirs(pic_dir)
            save_fn=os.path.join(pic_dir, new_fn)       
            file.save(save_fn)
            img = Image.open(save_fn)
            img=img.convert('RGB')
            quality=95
            resize_img=img.resize((720,960))
            img.save(save_fn, quality=quality)
            # target_size_kb=1300
            # step=10
            # while True:
            #     img.save(save_fn, quality=quality)
            #     #压缩图片                
            #     file_size_kb = os.path.getsize(save_fn) / 1024            
            #     if file_size_kb <= target_size_kb or quality == 0:
            #         break
            #     quality -= step
            #     if quality < 0:
            #         quality = 0
            pics_data=self.check_suibi_pic(date_input=date_input)
            pics=pics_data.get_json()

            return {"message": "上传成功","max_num":pics['max_num']},200
        else:
            return {"message": "只能上传图片"}, 400

    def ktt_import_order_page(self):
        return render_template('/ktt_import_order.html')

    #小红书图片及小红书封面
    def generate_xhs_page(self):
        return render_template('/generate_xhs.html')
    
    def generate_xhs_cover_page(self):
        return render_template('/generate_xhs_cover.html')
    
    def suibi_page(self):
        return render_template('/suibi.html')

    def ktt_tea_menu_page(self):
        return render_template('/ktt_tea_menu.html')
    # 
    def ktt_send_txt_page(self):
        return render_template('/ktt_send_txt.html')

    def ktt_query_tea_page(self):
        return render_template('/ktt_query_tea.html')

    def ktt_sale_tea_page(self):
        return render_template('/ktt_sale_tea.html')

    def ktt_purchase_tea_page(self):
        return render_template('/ktt_purchase_tea.html')
    
    def ktt_purchase_deal(self):
        data=request.json
        # print(data)
        conn=self.connect_mysql();
        cursor=conn.cursor();
        try:
            sql=f'select goods_id,goods_type,goods_name,goods_name2,goods_producer from products where goods_id=%s'
            # print(data['product_id'])
            cursor.execute(sql,data['product_id'])
            product_info=cursor.fetchall()
            # print(product_info)
            goods_id=data['product_id']
            goods_type=product_info[0][1]
            goods_name=product_info[0][2]
            cmt=data['cmt'] 
            total_price=data['total_price']
            flow_in_time=data['date_time']
            opr_name=data['opr_name']
            goods_unit=data['product_unit']
            current_time=datetime.now()
            opr_time=current_time.strftime('%Y-%m-%d %H:%M:%S')
            print(current_time,opr_time)

            print(goods_unit)
            if goods_unit=='g':
                # goods_unit=data['product_unit']
                goods_unit2=goods_unit
                goods_price=data['price']
                qty2=qty=data['qty']
        
            else:
                equal_g=data['equal_g']
                qty2=data['qty']
                qty=float(qty2)*float(equal_g)

                goods_price=float(total_price)/float(qty);
                goods_unit2=data['product_unit']
                goods_unit='g'

            values=(goods_id,goods_type,goods_name,goods_unit,qty,goods_price,total_price,goods_unit2,qty2,cmt,flow_in_time,opr_name,opr_time)
            print(values)

            print('qty2:',qty2)

            sql=f'''
                 insert into flow_purchase (goods_id,goods_type,goods_name,goods_unit,qty,goods_price,total_price,goods_unit2,qty2,cmt,flow_in_time,opr_name,opr_time) 
                 values 
                 (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            '''
            cursor.execute(sql,values)
            conn.commit()
            cursor.close()
            conn.close()     
            return  jsonify({'res':'ok','prompt':(flow_in_time,goods_name,qty2,goods_unit2,opr_name)})    
            
        except Exception as e:
            print(e)
            cursor.close()
            conn.close()
            return jsonify({'res':'failed'})



        return {'res':'ok'}

    def ktt_sale_deal(self):
        data=request.json
        # print(data)
        conn=self.connect_mysql()
        cursor=conn.cursor()
        try:
            sql=f'select spec_id,spec_unit,spec_name,goods_type,goods_type2,pkg_list from specs where spec_id=%s'
            # print(data['spec_id'])
            cursor.execute(sql,data['spec_id'])
            spec_info=cursor.fetchone()
            ####################################
            # 写入flow_sale表中，即销售流水，按销售规格记录：
            # id, goods_type, spec_id, spec_name, spec_unit, qty, spec_price, total_spec_price, use_for, cmt, sale_time, opr_name, opr_time
            # print(spec_info)
            goods_type=spec_info[3]
            spec_id=spec_info[0]
            spec_name=spec_info[2]
            spec_unit=spec_info[1]
            qty=data['qty']
            spec_price=data['price']
            total_price=data['total_price']
            use_for=data['use_for']
            cmt=data['cmt']
            sale_time=data['date_time']
            opr_name=data['opr_name']
            current_time=datetime.now()
            opr_time=current_time.strftime('%Y-%m-%d %H:%M:%S')

            values=(goods_type, spec_id, spec_name, spec_unit, qty, spec_price, total_price, use_for, cmt, sale_time, opr_name, opr_time)

            sql=f'''
                insert into flow_sale (goods_type, spec_id, spec_name, spec_unit, qty, spec_price, total_spec_price, use_for, cmt, sale_time, opr_name, opr_time) 
                values 
                (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            '''
            cursor.execute(sql,values)
            # conn.commit()

            ####################################
            # 写入flow_sale_product表中，也是销售流水，按进货的规格记录（通常为克（g)）
            # id, goods_id, goods_name, pkg_name, goods_unit, goods_qty, use_for, cmt, sale_time, opr_name, opr_time
            pkg_list=spec_info[5]
            pkgs=pkg_list.split(';')[:-1]
            for pkg in pkgs:
                
                pkg_id=pkg.split(',')[0]
                pkg_qty=float(pkg.split(',')[1])
                sql=f'select goods_list,pkg_name from packages where pkg_id=%s'
                cursor.execute(sql,pkg_id)
                goods_list=cursor.fetchone()
                sql=f'select goods_name,goods_name2,wt_unit from products where goods_id=%s'
                # print(goods_list)
                product_info=goods_list[0].split(';')[0].split(',')
                goods_id=product_info[0]
                # print(goods_id)
                cursor.execute(sql,goods_id)
                goods_info=cursor.fetchone()
                # print(goods_info)
                goods_name=goods_info[0]
                pkg_name=goods_list[1]
                goods_unit=goods_info[2]
                #总克数=每小包克数*小包数*规格数
                goods_qty=float(product_info[1])*pkg_qty*float(qty)
                # use_for
                # cmt
                # sale_time
                # opr_name
                # opr_time
                values=(goods_id,goods_name,pkg_name,goods_unit,goods_qty,use_for,cmt,sale_time,opr_name,opr_time)
                sql=f'''
                    insert into flow_sale_product (goods_id, goods_name, pkg_name, goods_unit, goods_qty, use_for, cmt, sale_time, opr_name, opr_time) 
                    values 
                    (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                '''
                cursor.execute(sql,values)


            conn.commit()
            cursor.close()
            conn.close()

        except Exception as e:
            conn.rollback()
            print(e)
            cursor.close()
            conn.close()
            return jsonify({'res':'failed'})



        return {'res':'ok','prompt':(sale_time,spec_name,qty,spec_unit,opr_name)}
    
    #计算库存 
    def ktt_stat_deal(self):
        conn=self.connect_mysql()
        cursor=conn.cursor()
        try:
            sql=f'''
                SELECT 
                p.goods_id, p.goods_name, SUM(p.qty) AS total_purchase_qty, 
                COALESCE((SELECT SUM(s.goods_qty) FROM flow_sale_product s WHERE s.goods_id = p.goods_id), 0) AS total_sale_qty, 
                (SUM(p.qty) - COALESCE((SELECT SUM(s.goods_qty) FROM flow_sale_product s WHERE s.goods_id = p.goods_id), 0)) AS current_stock 
                FROM flow_purchase p 
                GROUP BY p.goods_id, p.goods_name
            '''
            cursor.execute(sql)
            res=cursor.fetchall()
            # print(res)            
            return jsonify({'res':'ok','stocks':res})

        except Exception as e:
            conn.rollback()
            print(e)
            cursor.close()
            conn.close()
            return jsonify({'res':'failed'})
        
    def get_ktt_html_page_config(self):
        kttpage_config=self.ktt_config['ktt_html_page_config']
        with open(kttpage_config,'r',encoding='utf-8') as f:
            res=json.load(f)
        print(res) 
        return jsonify({'res':'ok','data':res})

    def riyun_input_page(self):
        return render_template('/riyun_input.html')

    def riyun(self):
        return render_template('/riyun.html')
        
    def riyun_menu(self):
        return render_template('/riyun_menu.html')

    def ktt(self):
        return render_template('/ktt.html')

    #获取商品规格
    def ktt_fetch_specs(self):
        print('connecting database specs...')
        conn=self.connect_mysql();
        cursor=conn.cursor();
        try: 
            #如果没有记录，写入salt值，如有，更新。
            sql=f'select spec_id,spec_unit,spec_name,goods_type,goods_type2,pkg_list,price,price2,price2_on_pkg,price_cmt from specs'
            cursor.execute(sql)
            res=cursor.fetchall()
            # print(res)
            cursor.close()
            conn.close()
            return jsonify({'res':'ok','specs':res})   
        except Exception as e:
            print(e)
            cursor.close()
            conn.close()
            return jsonify({'res':'failed'})

    #获取人员列表
    def ktt_fetch_opr(self):
        print('connecting opr database...')
        conn=self.connect_mysql();
        cursor=conn.cursor();
        try: 
            #如果没有记录，写入salt值，如有，更新。
            sql=f'select opr_id,opr_name from opr'
            cursor.execute(sql)
            res=cursor.fetchall()
            # print(res)
            cursor.close()
            conn.close()
            return jsonify({'res':'ok','opr':res})   
        except Exception as e:
            print(e)
            cursor.close()
            conn.close()
            return jsonify({'res':'failed'})

    #获取包装列表
    def ktt_fetch_pkgs(self):
        print('connecting package database...')
        conn=self.connect_mysql();
        cursor=conn.cursor();
        try: 
            sql=f'select pkg_id,pkg_name,goods_list from packages'
            cursor.execute(sql)
            res=cursor.fetchall()
            # print(res)
            cursor.close()
            conn.close()
            return jsonify({'res':'ok','pkgs':res})   
        except Exception as e:
            print(e)
            cursor.close()
            conn.close()
            return jsonify({'res':'failed'})

    #获取产品列表
    def ktt_fetch_product(self):
        print('connecting product database...')
        conn=self.connect_mysql();
        cursor=conn.cursor();
        try: 
            sql=f'select goods_id,goods_name,goods_producer from products'
            cursor.execute(sql)
            res=cursor.fetchall()
            # print(res)
            cursor.close()
            conn.close()
            return jsonify({'res':'ok','products':res})   
        except Exception as e:
            print(e)
            cursor.close()
            conn.close()
            return jsonify({'res':'failed'})

    def ktt_buy_list_page(self):
        # print(list(self.config_ktt_order.keys()))
        #读取config文件里的导单模板设置，并传送到前端
        # with open(self.ktt_config['ktt_col_map_config'],'r',encoding='utf-8') as f_colmap:
        #     colmap_config=json.loads(f_colmap.read())
        # # print(self.fn_info)
        
        #读取config文件里的导单模板设置，并传送到前端
        return render_template('/ktt_buy_list.html',expMode=list(self.config_ktt_order.keys()),
                                                    specName0=self.spec0,specName1=self.spec1,
                                                    senderNameDefault=self.sender_name,
                                                    senderTelDefault=self.sender_tel,
                                                    fnInfoDefault=self.fn_info)
    def ktt_return_spec(self):
        data=request.json
        supplier=data['supplier']
        specs=self.col_map_config[supplier]
        return {'res':'ok','data':specs}     
                                                    

    def ktt_deal_list(self):
        data=request.json
        try:
            fn_info=data['fn_info'].split('，')
        except:
            fn_info=data['fn_info'].split(',')

        exp_mode=data['exp_mode']
        sender_name=data['sender_name']
        sender_tel=data['sender_tel']
        spec0=data['spec0']
        buy_list0=data['buy_list0']
        spec1=data['spec1']
        buy_list1=data['buy_list1']
        spec2=data['spec2']
        buy_list2=data['buy_list2']
        spec3=data['spec3']
        buy_list3=data['buy_list3']
        spec4=data['spec4']
        buy_list4=data['buy_list4']
        spec5=data['spec5']
        buy_list5=data['buy_list5']
        spec6=data['spec6']
        buy_list6=data['buy_list6']
        spec7=data['spec7']
        buy_list7=data['buy_list7']
        # odrs=[[spec0,buy_list0],[spec1,buy_list1]]
        odrs=[]
        if spec0 and buy_list0:
            odrs.append([spec0,buy_list0])
        if spec1 and buy_list1:
            odrs.append([spec1,buy_list1])
        if spec2 and buy_list2:
            odrs.append([spec2,buy_list2])
        if spec3 and buy_list3:
            odrs.append([spec3,buy_list3])
        if spec4 and buy_list4:
            odrs.append([spec4,buy_list4])
        if spec5 and buy_list5:
            odrs.append([spec5,buy_list5])
        if spec6 and buy_list6:
            odrs.append([spec6,buy_list6])
        if spec7 and buy_list7:
            odrs.append([spec7,buy_list7])
    

        p=ktt_order_export.KttList()
        res=p.multi_spec_output(supplier=exp_mode,sender_name=sender_name,sender_tel=sender_tel,odrs=odrs,save='yes',save_cfg=fn_info,save_dir=self.ktt_config['save_dir'])
        return jsonify({'res':'ok','wx_txt':res['wx_txt']})

    def write_into_riyun_xlsx(self):
        print('writing into riyun xlsx')
        data=request.json
        y,m,d=data['date'].split('-')
        gz=ganzhi.GanZhi().cal_dateGZ(int(y),int(m),int(d),8)
        data['tg']=gz['bazi'][4]
        data['dz']=gz['bazi'][5]

        try:
            df=pd.DataFrame(data,index=[0])
            df=df[['date', 'weekday', 'tg', 'dz', 'daoyu','color-mu', 'txt-mu', 'color-huo', 'txt-huo', 'color-tu', 'txt-tu', 'color-jin', 'txt-jin', 'color-shui', 'txt-shui']]
            # print(df)
            with open(os.path.join(os.path.dirname(__file__),'config','riyun.config'),'r',encoding='utf-8') as f:
                config_ej=json.load(f)
            riyun_fn=config_ej['riyun_fn']

        ########
            # app=xw.App(visible=False)
            # wb=app.books.open(riyun_fn)
            # sheet=wb.sheets['运势']
            # last_row=sheet.range('A1048576').end('up').row
            
            # last_date=sheet.range(f'A{last_row}').value

            # date_input=datetime.strptime(data['date']+' 00:00:00','%Y-%m-%d %H:%M:%S')

            # # print(date_input)
            # # print(last_date)
    
            # dates=sheet.range(f'A3:A{last_row}').value
            # try:
            #     row_number=dates.index(date_input)+3
            # except:
            #     row_number=last_row+1
            # finally:            
            #     print(f'write into row {row_number}')
                
            #     # 将 DataFrame 数据写入 Excel 工作表的指定行号
            #     print(df.values.tolist)
            #     sheet.range(f'A{row_number}:N{row_number}').value=df.values

            #     wb.save(riyun_fn)
            #     wb.close()
            #     app.quit()
        
        #######

        # 打开 Excel 文件
            wb = openpyxl.load_workbook(riyun_fn)

            # 选择工作表
            sheet = wb['运势']

            # 获取最后一行的行号
            last_row = sheet.max_row

            # 获取最后一个日期
            last_date = sheet[f'A{last_row}'].value

            # 解析输入的日期
            date_input = datetime.strptime(data['date'] + ' 00:00:00', '%Y-%m-%d %H:%M:%S')

            # 查找日期在表中的位置
            dates = [sheet[f'A{i}'].value for i in range(3, last_row+1)]
            try:
                row_number = dates.index(date_input) + 3
            except ValueError:
                row_number = last_row + 1
            finally:
                print(f'write into row {row_number}')

                # 将 DataFrame 数据写入 Excel 工作表的指定行号
                for r_idx, row in enumerate(dataframe_to_rows(df, index=False,header=False), row_number):
                    for c_idx, value in enumerate(row, 1):
                        sheet.cell(row=r_idx, column=c_idx, value=value)

                # 保存并关闭工作簿
                wb.save(riyun_fn)
                wb.close()


        except Exception as e:
            print('write xlsx error',e)
            return {'res':'failed','error':'write xlsx error'}

        

        return {'res':'ok'}

    def run_week_txt_cover(self,fn_num,prd=['20220822','20220828'],sense_word_judge='yes',desc_txt_show='yes',file_types='img_txt'):
        work_dir=self.config_ej['work_dir']
        output_dir=self.config_ej['output_dir']
        if fn_num=='1':
            xls=os.path.join(work_dir,'运势','运势.xlsx')
        else:
            xls=os.path.join(work_dir,'运势','运势2.xlsx')

        try:

            eachday_output_dir=os.path.join(output_dir,'日穿搭')
            cover_save_dir=os.path.join(output_dir,'日穿搭','0-每周运势封图')

            print('\n正在处理每日穿搭配色图\n')
            week_pic=week_yun.ExportImage(work_dir=work_dir)            
            dec_txt=week_pic.batch_deal(prd=prd,out_put_dir=eachday_output_dir,xls=xls)        
             
       
            if dec_txt['res']=='ok':
                # print('\n正在处理每日穿搭配色图\n')
                # week_pic=week_yun.ExportImage(work_dir=work_dir)

                print('\n正在处理每日穿搭配色文案..\n')
                week_txts=week_yun.ExportWeekYunTxt(work_dir=work_dir,import_dec_dic=dec_txt)
                
                all_txt=week_txts.all_date_wx(prd=prd,xls=xls,save='yes',save_dir=eachday_output_dir,sense_word_judge=sense_word_judge,desc_txt_show=desc_txt_show)
                

                if file_types=='img_txt_video':
                    print('\n正在生成视频\n')
                    videos=week_yun.videoClips(work_dir=work_dir,import_dec_dic=dec_txt,import_txt=all_txt)
                    videos.batch_export_video(prd=prd,xls=xls,save='no',save_dir=eachday_output_dir,sense_word_judge='no')


                return {'res':'ok'}
            else:
                return {'res':'failed','error':dec_txt['error']}
        except Exception as e:
            # raise FERROR('error where generate riyun pics and txt')
            print(e)
            return {'res':'failed','error':'error when generate riyun pics and txt：'+str(e)}    
        
    def run_suibi(self,date_input='20240717',sb_xlsx='D:\\坚果云\\运势\\运势2.xlsx',output_dir='e:\\temp\\ejj\\日穿搭'):
        try:
            work_dir=self.config_ej['work_dir']
            p=week_yun.suiBiVideo(work_dir=work_dir,output_dir=output_dir)
            p.export_video(date_input=date_input,sb_xlsx=sb_xlsx)
            return {'res':'ok'}
        except Exception as e:
            return {'res':'failed','error':'sujiBiVideo.export_video()执行错误'}

            
    def generate_suibi(self):
        data=request.json
        date_input=data['dateInput']
        # format_date=f'{date_input[:4]}-{date_input[4:6]}-{date_input[6:]}'
        format_date=''.join(date_input.split('-'))
        fn_num=data['fnNum']
        if fn_num=='1':
            fn=f'运势.xlsx'
        else:
            fn=f'运势{fn_num}.xlsx'
        xls=os.path.join(self.config_ej['work_dir'],'运势',fn)
        print(date_input,fn_num,xls,format_date)
        output_dir=os.path.join(self.config_ej['output_dir'],'日穿搭')
        try:
            res_generate=self.run_suibi(date_input=format_date,sb_xlsx=xls,output_dir=output_dir)
       
            if res_generate['res']!='ok':
                return jsonify({'res':'failed','error':'run suibi() error'})
            
            else:
                return jsonify({'res':'ok','data':f'{date_input} 随笔视频生成OK'})

            
            # return zip
        except Exception as e:
            print('riyun() Error:',e)
            return jsonify({'res':'failed','error':e})
    

    def generate_riyun(self):
        data=request.data.decode('utf-8')
        fn_num,start_date,end_date,file_types=data.split('|')
        start_date_input=start_date.replace('-','')
        end_date_input=end_date.replace('-','')
        try:
            #desc_txt_show参数为是否需要饰品文字及音频
            res_generate=self.run_week_txt_cover(fn_num=fn_num,prd=[start_date_input,end_date_input],desc_txt_show='no',file_types=file_types)  
            # res_generate=run_week_txt_img_video(prd=[s,e],work_dir='D:\\工作目录\\ejj',xls=xls,output_dir='e:\\temp\\ejj',sense_word_judge='yes')
            # self.zip_and_download(prd=[start_date,end_date])  
            # print(res_generate)
            if res_generate['res']!='ok':
                return {'res':'failed','error':res_generate['error']}
            

            #将日期写入临时文件

            tmp_dir=tmp_fn=os.path.join(self.config_ej['output_dir'],'日穿搭','zip')
            if not os.path.exists(tmp_dir):
                os.makedirs(tmp_dir)
            tmp_fn=os.path.join(tmp_dir,'riyun_tmp')
            with open (tmp_fn, 'w') as f:
                f.write(f'{start_date},{end_date}')

            return {'res':'ok','res_data':f'{start_date},{end_date},OK'}

            
            # return zip
        except Exception as e:            
            print('riyun() Error:',e)
            return {'res':'failed','error':e}

    def zip_and_download(self):
        # prd_input=request.data.decode('utf-8')
        date_input=request.args.get('date')
        if not date_input:
            tmp_fn=os.path.join(self.config_ej['output_dir'],'日穿搭','zip','riyun_tmp')
            with open (tmp_fn, 'r') as f:
                prd_input=f.read()
            
            # print(prd_input)
            # prd_input='2023-08-22,2023-08-22'
            prd=prd_input.split(',')
        else:
            date_str=f'{date_input[:4]}-{date_input[4:6]}-{date_input[6:]}'
            prd=[date_str,date_str]


        print('zip_and_download() ',prd)
        if prd[0]==prd[1]:
            output_filename=prd[0]
        else:
            output_filename=prd[0]+'-'+prd[1]

        path=os.path.join(self.config_ej['output_dir'],'日穿搭')
        memory_file = io.BytesIO()
        with zipfile.ZipFile(memory_file, 'w', zipfile.ZIP_STORED) as zipf:
            for root, dir, files in os.walk(path):
                for file in files:                    
                    try:     
                        if datetime.strptime(prd[0],'%Y-%m-%d')<=datetime.strptime(root.split('\\')[-1],'%Y-%m-%d')<=datetime.strptime(prd[1],'%Y-%m-%d'):
                            if 'suiBi_video' not in file:
                                file_path = os.path.join(root, file)
                                arcname = os.path.relpath(file_path, path)
                                zipf.write(file_path,arcname)
          
                    except:
                        pass

        memory_file.seek(0)

        return Response(memory_file.getvalue(),
                        mimetype='application/zip',
                        headers={'Content-Disposition': f'attachment;filename={output_filename}.zip'})
    
    def zip_and_download_suibi(self):
        date_input=request.args.get('dateInput').split('.')[0]
        print('suibi zip and download:',date_input)
        # format_date=datetime.strftime(date_input,'%Y%m%d')
        format_date=''.join(date_input.split('-'))

        path=os.path.join(self.config_ej['output_dir'],'日穿搭')

        video_dir=os.path.join(path,f'{format_date[:4]}-{format_date[4:6]}-{format_date[6:]}')

        for fn in os.listdir(video_dir):
            if 'suiBi_video' in fn:
                output_filename=os.path.join(video_dir,f'{format_date}-suiBi_video.mp4')

        try:
            return send_file(output_filename,
                            mimetype='video/mp4',
                            as_attachment=True)
        except Exception as e:
            print(f"Error serving file: {e}")
            return Response(f"Error serving file: {e}", status=500)

        # return Response(memory_file.getvalue(),
        #                 mimetype='application/zip',
        #                 headers={'Content-Disposition': f'attachment;filename={output_filename}.zip'})

    def serve_single_file(self,output_filename):
        try:
            return send_file(output_filename,
                            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                            as_attachment=True)
        except Exception as e:
            print(f"Error serving file: {e}")
            return Response(f"Error serving file: {e}", status=500)
    
    def zip_and_download_mix_ktt(self):        
        fn=request.args.get('fn').split('.')[0]
        date_input=fn.split('-')[0]
        batch_num=fn.split('.')[0].split('-')[3]
        path=os.path.join(self.ktt_config['ktt_temp_dir'],'to_sender_xlsx',f'{date_input[:4]}-{date_input[4:6]}-{date_input[6:]}')
        output_filename=f'{date_input[:4]}-{date_input[4:6]}-{date_input[6:]}-{batch_num}'

        files_in_path = [os.path.join(path, f) for f in os.listdir(path) if os.path.isfile(os.path.join(path, f))]
        # print('files in path :',files_in_path)
    
        # 如果只有一个文件且是xlsx类型，则直接返回该文件
        # if len(files_in_path) == 1 and files_in_path[0].lower().endswith('.xlsx'):
        #     return self.serve_single_file(files_in_path[0])

        memory_file = io.BytesIO()    
        try:
            with zipfile.ZipFile(memory_file, 'w', zipfile.ZIP_STORED) as zipf:
                for root, dirs, files in os.walk(path):
                    for file in files:
                        try:
                            if file.split('-')[1]==batch_num:
                                file_path = os.path.join(root, file)
                                arcname = os.path.relpath(file_path, path)
                                # print('zip in for:',file_path,arcname)
                                zipf.write(file_path, arcname)
                        except Exception as e:
                            print(f"Error writing file {file_path} to zip: {e}")
                            continue
        except Exception as e:
            print(f"Error creating zip file: {e}")
            return Response(f"Error creating zip file: {e}", status=500)

        memory_file.seek(0)

        return Response(memory_file.getvalue(),
                        mimetype='application/zip',
                        headers={'Content-Disposition': f'attachment;filename={output_filename}.zip'})

    

    def download_orders(self):
        filename = request.args.get('filename')
        filepath = os.path.join(self.ktt_config['export_root_dir'], 'temp', filename)
        if os.path.exists(filepath):
            return send_file(filepath, as_attachment=True)
        return jsonify({'res': 'failed', 'msg': '文件不存在'})

    def zip_and_download_ktt_exp_order(self):
        # prd_input=request.data.decode('utf-8')
        tmp_fn=os.path.join(self.ktt_config['save_dir'],'newfn.tmp')
        with open (tmp_fn, 'r') as f:
            newfn=f.read()
        return send_file(os.path.join(self.ktt_config['save_dir'],newfn),as_attachment=True)

    
class FERROR(Exception):
    pass


if __name__ == '__main__':
    app = EjService(__name__)
    if len(sys.argv)>1:
        # print(f'服务器为：{sys.argv[1]}:5000')
        app.run(debug=True,host=sys.argv[1],port=5023)
    else:
        app.run(debug=True)

    # app.run(debug=True,host='127.0.0.1',port=5023)
    # app.run(debug=True,host='192.168.10.2',port=5000)
    # app.run(debug=True,host='192.168.1.41',port=5001)
    # app.run(debug=True,host='192.168.1.149',port=5000)
    # res=wecom_dir()
    # print(res)
